Combine two records in a single (1 Viewer)

[r621]

Hi Guys,

I need your help , I'm trying to combine two records in a single record, I've tried without results to use a crosstab query.
e.g. : Following a sample of the table :

TAG​	DESCRIPTION ​	FIELD 1​	FIELD 2​	FIELD 3​	FIELD 4​
AA-321A​	TEST1​	1​	2​	3​	4​
AA-321B​	TEST2​	5​	6​	7​	8​
AA-322A​	TEST3​	9​	10​	11​	12​
AA-322B​	TEST4​	13​	14​	15​	16​

I need to combine the records in a single line like following:

TAG​	DESCRIPTION ​	FIELD 1​	FIELD 2​	FIELD 3​	FIELD 4​	TAG2​	DESCRIPTION2 ​	FIELD 5​	FIELD 6​	FIELD 7​	FIELD 8​
AA-321A​	TEST1​	1​	2​	3​	4​	AA-321B​	TEST2​	5​	6​	7​	8​
AA-322A​	TEST3​	9​	10​	11​	12​	AA-322B​	TEST4​	13​	14​	15​	16​

The same tag AA-32x-A & AA-32x-B shalle be grouped in a single record, could you please help me? I've to use sql instructions?

 

[ebs17]

SELF JOIN over Left(Tag, 6)

But the table has little to do with database.

 

[Pat Hartman]

There is no reason to merge the columns into a single row. Please tell us the actual business objective and we'll help to find a solution.

 

[r621]

Basically I'm working with MS ACCESS, I've a DB which contains the device's TAGs, some devices can have associate two tags, A and B, I need to combine the two records into a single, for two reasons:
1) I need to create a single Report in MS Access with the data of the both records A & B
2) I need to create a Autocad script and for blocks attributes I need a single record

Hope is clear, thanks to who will help me

 

[ebs17]

create a single Report

That is already clear. But this does not change the fact that the TABLE is far from a meaningful structure in a CORRECT database. A database table has higher requirements than just a collection of rows and columns. However, you are probably less interested in real database work, but only in a quick result (processing of supplied data):

SELECT
   T1.*,
   T2.*
FROM
   (
      SELECT
         *,
         Left(Tag, 6) AS X
      FROM
         TableX
      WHERE
         Right(Tag, 1) = "A"
   ) AS T1
      INNER JOIN
         (
            SELECT
               *,
               Left(Tag, 6) AS X
            FROM
               TableX
            WHERE
               Right(Tag, 1) = "B"
         ) AS T2
         ON T1.X = T2.X

 

[arnelgp]

if the First table has fixed column, you can just create a
New table with same structure as the second table in post#1.
with simple VBA you can group the first record (without the suffix A or B).
then insert the records to the new table.

demo db attached.

 

[Pat Hartman]

For the export to the CAD program I think the query might be simpler:
Select t1.*, t2*
From Yourtable as t1 left join yourtable as t2 on t1.ID = t2.ID
Where t1.Type = "A" and t2.Type = "B"

If that doesn't work correctly, create two separate queries. Select "A" in one and Select "B" in the other. Then create a third query to just left join the two queries. This is the equivalent of what ebs17 posted. If the BE is Jet/ACE you will get better performance using this method than using the complex sub query method in post #5.

You NEVER have to create a temp table.

For a report, you don't need to concatenate the data. You can just use a standard query that selects the "ID" and both rows will be included.

 

[r621]

For the export to the CAD program I think the query might be simpler:
Select t1.*, t2*
From Yourtable as t1 left join yourtable as t2 on t1.ID = t2.ID
Where t1.Type = "A" and t2.Type = "B"

If that doesn't work correctly, create two separate queries. Select "A" in one and Select "B" in the other. Then create a third query to just left join the two queries. This is the equivalent of what ebs17 posted. If the BE is Jet/ACE you will get better performance using this method than using the complex sub query method in post #5.

You NEVER have to create a temp table.

For a report, you don't need to concatenate the data. You can just use a standard query that selects the "ID" and both rows will be included.

This query will concatenate all xx-xxx-A and xx-xxx-B in the same records, is it correct? I need to concatenate in the same record the tag with the eguale first part of e.g. AA-321-A and AA-321-B ... I have a lots of different tags with the same philosophy in ny table...

 

[Pat Hartman]

I think that's what I said. That is what you need for the CAD program, right? Did you try it?

 

[r621]

Yes basically this is what I need, but I need data divided per tag name as per second table in my first post

 

[ebs17]

... you will get better performance using this method ...

17 milliseconds, much less time than discussed here.

 

[Josef P.]

I would simply state that both variants should be equally fast, since the same execution plan is created.
It doesn't matter if you filter in the subselect or join or in the outer where statement. This can even optimize the Jet/ACE engine.
Note: it is filtered first and then joined.

--- Query1 ---

- Inputs to Query -
Table 'InputT'
Table 'InputT'
- End inputs to Query -

01) Restrict rows of table InputT
by scanning
testing expression "right([Tag],1)='B'"
02) Restrict rows of table InputT
by scanning
testing expression "right([Tag],1)='A'"
store result in temporary table
03) Inner Join result of '01)' to result of '02)'
using X-Prod join
then test expression "Left([Tag],6)=Left([Tag],6)"


--- Query2 ---

- Inputs to Query -
Table 'InputT'
Table 'InputT'
- End inputs to Query -

01) Restrict rows of table InputT
by scanning
testing expression "Right(Tag,1)="B""
02) Restrict rows of table InputT
by scanning
testing expression "Right(Tag,1)="A""
store result in temporary table
03) Inner Join result of '01)' to result of '02)'
using X-Prod join
then test expression "Left(Tag,6)=Left(Tag,6)"

SQL:

Query1
SELECT
     T1.ID, T1.TAG, T1.DESCRIPTION, T2.ID, T2.TAG, T2.DESCRIPTION
FROM
    InputQ AS T1
    INNER JOIN
    InputQ AS T2 ON T1.Tag1 = T2.Tag1
WHERE
   T1.Tag2 = 'A' and T2.Tag2 = 'B'

InputQ:
SELECT
    InputT.ID
    , Left([Tag],6) AS Tag1, right([Tag],1) AS Tag2
    , InputT.TAG
   , InputT.DESCRIPTION, InputT.[FIELD 1], ....
FROM InputT

Query2:

SELECT
      T1.ID, T1.TAG, T1.DESCRIPTION, T2.ID, T2.TAG, T2.DESCRIPTION
FROM
     (
        SELECT Left(Tag,6) AS X, *
        FROM InputT
       WHERE Right(Tag,1)="A"
     )  AS T1
     INNER JOIN (
        SELECT Left(Tag,6) AS X, *
         FROM InputT WHERE Right(Tag,1)="B"
     )  AS T2 ON T1.X = T2.X;

 

[ebs17]

If you want to have a big performance debate, you should start splitting and indexing field contents from TAG in the table. Then one saves the calculated splitting in the query and gets into the position to use indexes.

The ID field used by Pat for linking does not yet exist, and it is left open whether it corresponds to the six characters of TAG. Since it can't fall from the sky on its own, it would have to be created.

 

[r621]

This sql code will work also in MS access?

 

[ebs17]

In an Access forum, without naming any other DBMS, one will of course primarily formulate SQL running in Access (Jet). So yes.

You are still thinking and don't dare to try?

 

[Josef P.]

If you want to have a big performance debate, ...

Thanks, not interested, the SQL statement is too unexciting for that.

The ID field used by Pat for linking does not yet exist, and it is left open whether it corresponds to the six characters of TAG. Since it can't fall from the sky on its own, it would have to be created.

That's why I adapted this SQL statement.
Regardless of index usage, I wanted to show that it doesn't matter where you put the criteria.

select
    T1.*, T2*
from
    Table T1
    inner join
    Table T2 ON T2.X = T1.X
where
    T1.Y = 'A'
    and
    T2.Y = 'B'

is equal to

select
    T1.*, T2*
from
    (select * from Table where Y = 'A') as T1
    inner join
    (select * from Table where Y = 'B') as T2 ON T2.X = T1.X

The Jet Engine performs this identically. => The 1st SQL statement is not better in terms of performance.

 

[r621]

If you want to have a big performance debate, you should start splitting and indexing field contents from TAG in the table. Then one saves the calculated splitting in the query and gets into the position to use indexes.

The ID field used by Pat for linking does not yet exist, and it is left open whether it corresponds to the six characters of TAG. Since it can't fall from the sky on its own, it would have to be created.

Thanks, not interested, the SQL statement is too unexciting for that.


That's why I adapted this SQL statement.
Regardless of index usage, I wanted to show that it doesn't matter where you put the criteria.

select
    T1.*, T2*
from
    Table T1
    inner join
    Table T2 ON T2.X = T1.X
where
    T1.Y = 'A'
    and
    T2.Y = 'B'

is equal to

select
    T1.*, T2*
from
    (select * from Table where Y = 'A') as T1
    inner join
    (select * from Table where Y = 'B') as T2 ON T2.X = T1.X

The Jet Engine performs this identically. => The 1st SQL statement is not better in terms of performance.

I think

Thanks, not interested, the SQL statement is too unexciting for that.


That's why I adapted this SQL statement.
Regardless of index usage, I wanted to show that it doesn't matter where you put the criteria.

select
    T1.*, T2*
from
    Table T1
    inner join
    Table T2 ON T2.X = T1.X
where
    T1.Y = 'A'
    and
    T2.Y = 'B'

is equal to

select
    T1.*, T2*
from
    (select * from Table where Y = 'A') as T1
    inner join
    (select * from Table where Y = 'B') as T2 ON T2.X = T1.X

The Jet Engine performs this identically. => The 1st SQL statement is not better in terms of performance.

ON the final query i guess that some code is missing.....

 

[Josef P.]

The SQL statements from #16 are only to show the principle. They do not match your data.

 

[ebs17]

@r621: I have shown a suggestion above that shows all the steps and implements them in a query. If the imaginary table name TableX is replaced by the existing one, the query should work immediately. Replace the name, accept the query and try it out, you should be able to do that, if not, you better find a service provider.

The statement to store all single steps in single queries and everything will be fine and suggests ten times as fast I think is nonsense. Serious investigations would confirm that, Josef showed approaches and results to it. But you can test this yourself as a second attempt.
Sometimes you have to see, feel, experience to have an answer to believe.

 

[Pat Hartman]

Yes basically this is what I need, but I need data divided per tag name as per second table in my first post

Did you try any of the solutions yet?