CODEWITHSUNDEEP
sqlsql-servert-sql
nzsquall
nzsquall

Reputation: 405

Sql table, join column values

I have a list of tables for example A, B C.

I don't know how many columns, nor all the column names for these tables.

What I know for each table is the key name and the column I want to combine.

A typical DDL for a table will be:

CREATE TABLE [dbo].[G_bHQ_S1](
[Key_code] [nvarchar](255) NULL,
[array1_nbr] [int] NULL,
[fDSHhldDetails] [int] NULL,
[fRespName] [nvarchar](200) NULL,
[fStoreAge] [int] NULL,
[qSex] [int] NULL,
[qDOB] [datetime] NULL,
[qDOBNR] [int] NULL,
[qAge] [int] NULL,
[qAgeNR] [int] NULL,
[qAgeRange] [int] NULL,
[qAge15OrOver] [int] NULL
) ON [PRIMARY]

The data stored for the first three columns will be similar as:

Key_code            array1_nbr  fDSHhldDetails      ........
W801053126H001  11          11                  ........
W802078001H001  11          11                  ........  
W802078012H001  11          11                  ........                  
W802078012H001  12          12                  ........  
W802078022H001  11          11                  ........  
W802078022H001  12          12                  ........  
........................................................

Each table will only have one column that I want to combine, and it will always be array1_nbr.

For each unique Key_code, I need the value of array1_nbr to be combine with the column name of its right next column.

So the result set for the above dataset will be:

Key_code                fDSHhldDetails_11     fDSHhldDetails_12     ........
W801053126H001          11                        null                  ........
W802078001H001          11                        null                  ........  
W802078012H001          11                        12                    ........                  
W802078022H001          11                        12                    ........  
....................................................................................

Can you please help me with this please?

Thanks.

Result set for @sgeddes:

Key_code                11                        12                ........
W801053126H001          11                        null              ........

The column names I am expecting are fDSHhldDetails_11 and fDSHhldDetails_12. And fDSHhldDetails is unknown for each table, but it is the column name right next to array1_nbr. Is there a way to concatenate it? Otherwise I can add it manually, the query is good enough for me already. Thanks heaps.

Upvotes: 0

Views: 595

Answers (1)

sgeddes
sgeddes

Reputation: 62841

Assuming you know the number of possible values of the array1_nbr column, then you can PIVOT the results.

Here's one way with MAX and CASE:

SELECT Key_Code,
  MAX(CASE WHEN array1_nbr = 11 THEN fDSHhldDetails END) fDSHhldDetails11,
  MAX(CASE WHEN array1_nbr = 12 THEN fDSHhldDetails END) fDSHhldDetails12
FROM YourTable
GROUP BY Key_Code

Here's with a PIVOT:

SELECT Key_Code, [11] fDSHhldDetails11, [12] 
FROM 
(
  SELECT Key_Code, array1_nbr
  FROM YourTable) p
PIVOT
(
  MAX(array1_nbr)
  FOR array1_nbr IN
  ( [11], [12] )
) pvt;

Alternatively, if you don't know the number of columns, then look into using Dynamic SQL.

Here's that example:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(array1_nbr) 
            FROM YourTable c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'SELECT Key_code, ' + @cols + ' 
              FROM 
              (
                SELECT Key_Code, array1_nbr
                FROM YourTable
               ) p
              PIVOT 
              (
                 MAX(array1_nbr)
                 for array1_nbr in (' + @cols + ')
              ) p '


EXECUTE(@query)

Upvotes: 2

Related Questions