Convert Rows into Columns

Question

Post reply

Convert Rows into Columns

lokesh

SSC Enthusiast

Points: 177

August 2, 2019 at 1:15 pm

#3668252

Hello All,

I'm trying to convert rows in table to columns as per the example i.e create one long string of data for every occurrence of ID column. Could anyone please help in achieving it in best way.

/** Build up a Table to work with. **/
DECLARE @T TABLE
    (
    ID INT NOT NULL 
    , hProp INT NOT NULL
    , iDayOfMonth INT NOT NULL
    , dblTargetPercent DECIMAL(6,4) NOT NULL
    )

INSERT INTO @T
(ID, hProp, iDayOfMonth, dblTargetPercent)
VALUES (117,10,5,0.1400)
        , (117, 10, 10, 0.0500) 
        , (117, 10, 15, 0.0100)
        , (117, 10, 20, 0.0100)
, (118, 20, 20, 0.0100)
, (118, 30, 10, 0.0200)

SELECT * FROM @T;

OutputData

Thanks and Regards,

Loki

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18271 More actions · Answer 1

Jonathan AC Roberts

SSCoach

Points: 18271

August 2, 2019 at 1:26 pm

#3668255

Can you give the expected output?

lokesh SSC Enthusiast Points: 177 More actions · Answer 2

Thanks Jonathan for your response.

I've attached sample output.

OutputData

Jonathan AC Roberts SSCoach Points: 18271 More actions · Answer 3

Something like this?

;WITH CTE AS 
(
    SELECT DISTINCT T.ID 
      FROM @T T
)
SELECT * 
  FROM CTE
 CROSS APPLY(SELECT STUFF(( SELECT CHAR(9) + CONCAT(hProp, CHAR(9), iDayOfMonth, CHAR(9),dblTargetPercent)
                              FROM @T T
                             WHERE T.Id = CTE.Id
                               FOR XML PATH(''),TYPE).value('.','nvarchar(MAX)'),1,1,'') X) AS Line

As the columns are tab-separated you should be able to paste the results directly into Excel.

To make the grid keep tabs in within a cell you need to make sure the checkbox Tools/Options.../Query Results/Results to Grid shown below is checked.

TabSSMS

lokesh SSC Enthusiast Points: 177 More actions · Answer 4

Thanks Jonathan for the query... 🙂

Output data is coming correctly but is it possible to get output data into corresponding pivoted columns.

I attached sample data in excel for better understanding but in actual need output in database with columns repeating with same number of records and occurrence of ID.

Thanks & Regards,

Loki

Jeff Moden SSC Guru Points: 1003895 More actions · Answer 5

lokesh wrote:

Thanks Jonathan for the query... 🙂
Output data is coming correctly but is it possible to get output data into corresponding pivoted columns.
I attached sample data in excel for better understanding but in actual need output in database with columns repeating with same number of records and occurrence of ID.
Thanks & Regards,
Loki

Yes, but it will require dynamic SQL if you want it to auto-magically determine how many sets of columns to use based on the number of rows for each ID.

I first recommend that you read the first article below to understand the concept of static "CrossTabs"and then read the second article for how to do it all dynamically.

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%e2%80%93-converting-rows-to-columns-1

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

[EDIT] AND... Jonathan's example below is a great example of a "Static CrossTab", which is covered in the first article.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18271 More actions · Answer 6

If you know the maximum number of rows for any Id in the table you could construct a query like this:

 SELECT Id, 
        MAX(IIF(RowNum = 1,hProp,NULL)) hProp,
        MAX(IIF(RowNum = 1,iDayOfMonth,NULL)) iDayOfMonth,
        MAX(IIF(RowNum = 1,dblTargetPercent,NULL)) dblTargetPercent,
        MAX(IIF(RowNum = 2,hProp,NULL)) hProp,
        MAX(IIF(RowNum = 2,iDayOfMonth,NULL)) iDayOfMonth,
        MAX(IIF(RowNum = 2,dblTargetPercent,NULL)) dblTargetPercent,
        MAX(IIF(RowNum = 3,hProp,NULL)) hProp,
        MAX(IIF(RowNum = 3,iDayOfMonth,NULL)) iDayOfMonth,
        MAX(IIF(RowNum = 3,dblTargetPercent,NULL)) dblTargetPercent,
        MAX(IIF(RowNum = 4,hProp,NULL)) hProp,
        MAX(IIF(RowNum = 4,iDayOfMonth,NULL)) iDayOfMonth,
        MAX(IIF(RowNum = 4,dblTargetPercent,NULL)) dblTargetPercent,
        MAX(IIF(RowNum > 4,'ToManyRowsForId',NULL)) RowInfo
   FROM (SELECT ROW_NUMBER() OVER (PARTITION BY T.ID ORDER BY T.iDayOfMonth) RowNum, *
           FROM @T T) X
  GROUP BY X.Id

Jeff Moden SSC Guru Points: 1003895 More actions · Answer 7

Unfortunately, this is a 2008 forum and so the IIF functionality will need to be converted to CASE statements if you're truly using 2008.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18271 More actions · Answer 8

Jeff Moden wrote:

Unfortunately, this is a 2008 forum and so the IIF functionality will need to be converted to CASE statements if you're truly using 2008.

Sorry, I didn't read which forum it was from.

SELECT X.Id, 
        MAX(CASE WHEN RowNum = 1 THEN hProp ELSE NULL END) hProp, 
        MAX(CASE WHEN RowNum = 1 THEN iDayOfMonth ELSE NULL END) iDayOfMonth,
        MAX(CASE WHEN RowNum = 1 THEN dblTargetPercent ELSE NULL END) dblTargetPercent,
        MAX(CASE WHEN RowNum = 2 THEN hProp ELSE NULL END) hProp, 
        MAX(CASE WHEN RowNum = 2 THEN iDayOfMonth ELSE NULL END) iDayOfMonth,
        MAX(CASE WHEN RowNum = 2 THEN dblTargetPercent ELSE NULL END) dblTargetPercent,
        MAX(CASE WHEN RowNum = 3 THEN hProp ELSE NULL END) hProp, 
        MAX(CASE WHEN RowNum = 3 THEN iDayOfMonth ELSE NULL END) iDayOfMonth,
        MAX(CASE WHEN RowNum = 3 THEN dblTargetPercent ELSE NULL END) dblTargetPercent,
        MAX(CASE WHEN RowNum = 4 THEN hProp ELSE NULL END) hProp, 
        MAX(CASE WHEN RowNum = 4 THEN iDayOfMonth ELSE NULL END) iDayOfMonth,
        MAX(CASE WHEN RowNum = 4 THEN dblTargetPercent ELSE NULL END) dblTargetPercent,
        MAX(CASE WHEN RowNum > 4 THEN 'ToManyRowsForId' ELSE NULL END) RowInfo
   FROM (SELECT ROW_NUMBER() OVER (PARTITION BY T.ID ORDER BY T.iDayOfMonth) RowNum, *
           FROM @T T) X
  GROUP BY X.Id

Jeff Moden SSC Guru Points: 1003895 More actions · Answer 9

Jonathan AC Roberts wrote:

Sorry, I didn't read which forum it was from.

I end up having that same problem. I did the very same thing yesterday.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lokesh SSC Enthusiast Points: 177 More actions · Answer 10

Thanks Jonathan for quickly responding and helping out.... 🙂 🙂

This exactly what I was looking for.

I will modify query and make it dynamic.

Thanks & Regards,

Loki