How to transpose the data

Question

Hi All,

Can someone please help me on how to transpose the data, below is my DDL and attached are the input and output :

Create Table #Temp
(
ID Int,
A_10 Int,
A_11 Int,
A_12 Int,
B_10 Int,
B_11 Int,
B_12 Int
)

Insert Into #Temp Values ( 1234,NULL,NULL,NULL,NULL,NULL,NULL)
Insert Into #Temp Values ( 5678,NULL,NULL,0,NULL,1,1)
Insert Into #Temp Values ( 9012,NULL,0,NULL,NULL,NULL,0)
Insert Into #Temp Values ( 8956,NULL,1,NULL,1,0,1)

Select * From #Temp

Drop Table #Temp

Basically what i am trying to achieve is any ID where one of the columns has a value of 1 i need that column name if there are multiple i need them all in 1 row.

Answer 1

Please try the following solution.
It shows power of XQuery and FLWOR expression.
Number of columns could be flexible, i.e. anything but column ID.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID Int, A_10 INT, A_11 INT, A_12 INT, B_10 INT, B_11 INT, B_12 INT);

INSERT INTO @tbl VALUES 
( 1234,NULL,NULL,NULL,NULL,NULL,NULL),
( 5678,NULL,NULL,0,NULL,1,1),
( 9012,NULL,0,NULL,NULL,NULL,0),
( 8956,NULL,1,NULL,1,0,1);
-- DDL and sample data population, end

SELECT ID
, REPLACE((SELECT (
 SELECT *
 FROM @tbl AS c
 WHERE c.ID = p.id
 FOR XML PATH('r'), TYPE, ROOT('root')).query('
 for $x in /root/r/*[local-name(.)!="ID" and ./text() = "1"]
 return local-name($x)
 ').value('.','VARCHAR(MAX)')),SPACE(1),',') AS column_list
FROM @tbl AS p;

Output

+------+----------------+
|  ID  |  column_list   |
+------+----------------+
| 1234 |                |
| 5678 | B_11,B_12      |
| 9012 |                |
| 8956 | A_11,B_10,B_12 |
+------+----------------+

Answer 2

Check this method too:

select ID, concat_ws( ', ', 
    nullif( replicate( 'A_10', A_10), ''),
    nullif( replicate( 'A_11', A_11), ''), 
    nullif( replicate( 'A_12', A_12), ''), 
    nullif( replicate( 'B_10', B_10), ''), 
    nullif( replicate( 'B_11', B_11), ''), 
    nullif( replicate( 'B_12', B_12), '') 
    ) as columnswith1
from #Temp

If it does not work, then show the version of your SQL Server.

Answer 3

Llike Viorel, I wrote a solution based on concat_ws, but then I saw that Sujith is on SQL 2016. There is a solution that works on that version:

; WITH CTE AS (
   SELECT ID, concat(CASE A_10 WHEN 1 THEN '[A_10],' END,
                     CASE A_11 WHEN 1 THEN '[A_11],' END,
                     CASE A_12 WHEN 1 THEN '[A_12],' END,
                     CASE B_10 WHEN 1 THEN '[B_10],' END,
                     CASE B_11 WHEN 1 THEN '[B_11],' END,
                     CASE B_12 WHEN 1 THEN '[B_12],' END) AS str
   FROM   #Temp
)
SELECT ID, iif(len(str) > 0, substring(str, 1, len(str) - 1), '')
FROM  CTE

By the way, you are on an old build of SQL 2016. You should apply SP2 and the latest Cumulative Update.