CODEWITHSUNDEEP
sqlsql-server-2008
TtT23
TtT23

Reputation: 7030

Dynamically append columns based on another table in SQL

Suppose I want the following behavior in SQL

QTY Table 

SERIAL_NO   QTY  CODE
1111111     1          AA
1111112     1          AA     
1111111     2          BB
1111111     4          BB
1111113     7          CC


Code Table

CODE CODE_NAME
 AA    NameA
 BB    NameB
 CC    NameC


Query Result

SERIAL_NO   NameA   NameB   NameC               
1111111      1        6       0
1111112      1        0       0
1111113      0        0       7

NameA,B,C column in query result are basically the sums grouped by their respective code.

How do I achieve this behavior? The hardest part I'm trying to grasp is to dynamically add the columns to the query result based on the code table. For example, if the user adds another code called DD, the query result should automatically append NameD to the right side, and get the corresponding sum for that new code.

Upvotes: 0

Views: 357

Answers (1)

peterm
peterm

Reputation: 92795

If you really need it in SQL you can leverage PIVOT and dynamic SQL in the following way

DECLARE @cols NVARCHAR(MAX), @colp NVARCHAR(MAX), @sql NVARCHAR(MAX)

SET @cols = STUFF(
(
  SELECT DISTINCT ',COALESCE(' + QUOTENAME(code_name) + ',0) AS ' + QUOTENAME(code_name)
    FROM code 
         FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @colp = STUFF(
(
  SELECT DISTINCT ',' + QUOTENAME(code_name)
    FROM code 
         FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @sql = 'SELECT serial_no, ' + @cols +   
           '  FROM 
            (
              SELECT serial_no, code_name, qty
                FROM qty q JOIN code c
                  ON q.code = c.code
            ) x
            PIVOT 
            (
               SUM(qty) FOR code_name IN (' + @colp + ')
            ) p
            ORDER BY serial_no'

EXECUTE(@sql)

Output:

| SERIAL_NO | NAMEA | NAMEB | NAMEC |
|-----------|-------|-------|-------|
|   1111111 |     1 |     6 |     0 |
|   1111112 |     1 |     0 |     0 |
|   1111113 |     0 |     0 |     7 |

Here is SQLFiddle demo

Upvotes: 2

Related Questions