How to convert Rows into Columns based on value

Question

I have following data in a table, in table each group contain two values for ON/OFF in two rows. I want to convert rows into new columns "Col1=>Col1_Status_ON/OFF" new name based on status

here is the detail:

====================================================
| Group  | DateTime    | Status | Col1   |  Col2   |
====================================================
| Group1 | 01-Jan-2019 |  ON    | 101    |  102    |
| Group1 | 01-Jan-2019 |  OFF   | 201    |  202    |
| Group2 | 01-Jan-2019 |  ON    | 301    |  302    |
| Group2 | 01-Jan-2019 |  OFF   | 401    |  402    |
| Group3 | 01-Jan-2019 |  ON    | 501    |  502    |
| Group4 | 01-Jan-2019 |  OFF   | 601    |  602    |
====================================================

I want query that return as follows

    =======================================================================================
        | Group  | DateTime   |Col1_Satus_ON|Col1_Satus_OFF|Col2_Status_ON|Col2_Status_OFF|
    =======================================================================================
        | Group1 | 01-Jan-2019|   101       |   201        |    102       |   202         |
        | Group2 | 01-Jan-2019|   301       |   401        |    302       |   402         |
        | Group3 | 01-Jan-2019|   501       |   601        |    502       |   602         |        
    =======================================================================================

below are the SQL to generate table data.

DROP TABLE IF EXISTS dbo.#MyTable;
SELECT CONVERT(VARCHAR(100),'')[Group], GETDATE()[DateTime],
       CONVERT(VARCHAR(100),'')[Status],(0)[Col1],(0)[Col2] 
       INTO #MyTable WHERE 1=2;

INSERT INTO #MyTable ([Group],[Datetime],[Status],[Col1],[Col2])
    SELECT 'Group1','01-Jan-2019 01:02:03','ON' ,101,102 UNION
    SELECT 'Group1','01-Jan-2019 01:02:03','OFF',201,202 UNION
    SELECT 'Group2','01-Jan-2019 01:02:03','ON' ,301,302 UNION
    SELECT 'Group2','01-Jan-2019 01:02:03','OFF',401,402 UNION
    SELECT 'Group3','01-Jan-2019 01:02:03','ON' ,501,502 UNION
    SELECT 'Group3','01-Jan-2019 01:02:03','OFF',601,602;

SELECT * FROM #MyTable;

please suggest the query,

If at all possible you should consider not using reserved words as column names. It is allowed but is a real pain to work with. The reserved words typically are so vague they don't make great column names anyway. — Sean Lange, Jan 21 '19 at 21:05
@Haseeb see the query in answer. If you have multiple columns take look at dynamic query or at this post : https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Sanpas, Jan 21 '19 at 21:36

score 3 · Answer 1 · answered Jan 21 '19 at 21:01

3

You could use conditional aggregation:

SELECT [Group], [Datetime],
    Col1_Status_ON  = MIN(IIF(Status='ON',  Col1, NULL)),
    Col1_Status_OFF = MIN(IIF(Status='OFF', Col1, NULL)),
    Col2_Status_ON  = MIN(IIF(Status='ON',  Col2, NULL)),
    Col2_Status_OFF = MIN(IIF(Status='OFF', Col2, NULL))
FROM #Mytable
GROUP BY [Group], [Datetime];

db<>fiddle demo

answered Jan 21 '19 at 21:01

Lukasz Szozda

162,964
23
234
275

thx but above query fit for fixed columns but what about if columns are not fixed and count of col vary like col1,col2,col3 to colx. – Haseeb Jan 21 '19 at 21:24
1

@Haseeb this is the query. If you have dynamic number off columns you have to combine thoses in a dynamic sql query . – Sanpas Jan 21 '19 at 21:33
@pascalsanchez ok I got it you are right!! If couldn't find best answer then I will mark your answer. :) many thanks – Haseeb Jan 21 '19 at 21:42

John Cappelletti · Accepted Answer · 2019-01-21T23:06:09.000

Here is a working dynamic pivot as folks suggested

Example

Declare @UnPiv varchar(max),@SQL varchar(max)
Set @UnPiv = Stuff(( Select ','+quotename(name)
                      From  sys.dm_exec_describe_first_result_set(N'Select top 1 * from #MyTable',null,null )  A
                      Where Name not in ('Group','DateTime','Status')
                      For XML Path('')),1,1,'')

Set @SQL   = Stuff(( Select ','+quotename(name+Suffix)
                      From  sys.dm_exec_describe_first_result_set(N'Select top 1 * from #MyTable',null,null )  A
                      Cross Join ( values ('_Status_ON')
                                         ,('_Status_OFF')
                                 ) B(Suffix)
                       Where Name not in ('Group','DateTime','Status')
                       For XML Path('')),1,1,'')

Select @SQL = '
Select *
 From  (
        Select [Group]
              ,[DateTime]
              ,Item = concat(Item,''_Status_'',Status)
              ,Value
         From  (
                 Select * From #MyTable Unpivot (Value for Item in ('+@UnPiv+')) UnPiv
               ) A
       ) src
 Pivot (max(Value) for Item in ('+@SQL+') ) pvt
'
--Print @SQL
Exec(@SQL)

Returns

How to convert Rows into Columns based on value

2 Answers2