Converting rows to columns

Question

I have a query with the columns 'Name', 'Amount', and 'ReasonId'. I want to sum the amount and put the reasons on one row to keep every name to a single line. There are about 50 distinct ReasonId's so I do not want to name the column the name of the ReasonId's. Instead, I would like to name the columns 'Reason1', 'Reason2', 'Reason3', and 'Reason4'. One single name can have up to 4 different reasons.

I have this:

Name   Amount   ReasonId
-------------------------
Bob    $5       7
Bob    $8       6
John   $2       8
John   $5       9
John   $3       9
John   $8       4

I want to produce the following:

Name   Amount   Reason1   Reason2   Reason3   Reason4
-----------------------------------------------------
Bob    $13      7         6         NULL      NULL
John   $18      8         9         4         NULL

Possible duplicate of [Efficiently convert rows to columns in sql server](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Tab Alleman, Mar 04 '16 at 16:16
The way the question is posted,it my not be of much help..Could you please add some sample data..http://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/ — TheGameiswar, Mar 04 '16 at 16:29
Have a look at this: [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — CaseyR, Mar 04 '16 at 17:00
He wants to scape repeated ReasonId. But like this souldn't be necessary Reason4 on desired result, because he will just have 3 results as Reason for John. — Andrew Paes, Mar 04 '16 at 17:02

score 0 · Answer 1 · answered Mar 04 '16 at 17:02

One way to do this is to use the dense_rank window function to number the rows, and then use conditional aggregation to put the reason in the correct columns.

I can't see anything that would give the specific order of the reason columns though, maybe there is some column missing that provides the order?

with cte as (
    select 
       name, 
       reasonid,
       amount, 
       dense_rank() over (partition by name order by reasonid) rn
    from your_table
)

select 
    name, 
    sum(amount) amount, 
    max(case when rn = 1 then reasonid end) reason1, 
    max(case when rn = 2 then reasonid end) reason2, 
    max(case when rn = 3 then reasonid end) reason3, 
    max(case when rn = 4 then reasonid end) reason4 
from cte 
group by name

If you have some column that gives the order you want then change the order by clause used in the dense_rank function.

Sample SQL Fiddle (using PG as MSSQL seems to be offline).

The output from the query above would be:

| name | amount | reason1 | reason2 | reason3 | reason4 |
|------|--------|---------|---------|---------|---------|
|  Bob |     13 |       6 |       7 |  (null) |  (null) |
| John |     18 |       4 |       8 |       9 |  (null) |

score 0 · Answer 2 · answered Mar 04 '16 at 17:16

You could also use a pivot to achieve this; if you know the columns you can enter them in the script, but if not, you can use dynamic sql (there are reasons why you might want to avoid the dynamic solution).

The advantage of this route is that you can enter the column list in a table and then changes to that table will result in changes to your output with change to the script involved. The disadvantages are all those associated with dynamic SQL.

In the interests of variation, here is a dynamic SQL solution using temp tables to hold your data, since a different possibility has been provided:

-- set up your data
CREATE TABLE #MyTab (Name VARCHAR(4), Amount INT, ReasonId INT)
CREATE TABLE #AllPossibleReasons (Id INT,Label VARCHAR(10))

INSERT #AllPossibleReasons
VALUES
     (1,'Reason1')
    ,(2,'Reason2')
    ,(3,'Reason3')
    ,(4,'Reason4')
    ,(5,'Reason5')
    ,(6,'Reason6')
    ,(7,'Reason7')
    ,(8,'Reason8')
    ,(9,'Reason9')

INSERT #MyTab
VALUES
     ('Bob',7,7)
    ,('Bob',8,6)
    ,('John',2,8)
    ,('John',5,9)
    ,('John',3,9)
    ,('John',8,4)
-----------------------------------------------------------------------------
-- The actual query
DECLARE @ReasonList VARCHAR(MAX) = ''
DECLARE @SQL VARCHAR(MAX)

SELECT @ReasonList = @ReasonList + ',' + QUOTENAME(Label)
FROM #AllPossibleReasons
SET @ReasonList = SUBSTRING(@ReasonList,2,LEN(@ReasonList))

SET @SQL = 
'SELECT Name,Value,' + @ReasonList + ' FROM
    (SELECT 
        M.Name,SUM(Amount) AS This, Label, SUM(Total.Value) AS Value
     FROM
            #MyTab                              AS M
     INNER JOIN #AllPossibleReasons             AS Reason   ON M.ReasonId = Reason.Id
     INNER JOIN(SELECT T.Name, SUM(Amount)Value
                FROM #MyTab T GROUP BY T.Name)      AS Total    ON M.Name = Total.Name
     GROUP BY M.Name, Reason.Label) AS Up
    PIVOT (SUM(THis) FOR Label IN (' + @ReasonList + ')) AS Pvt'

EXEC (@SQL)

DROP TABLE #AllPossibleReasons
DROP TABLE #MyTab

score 0 · Answer 3 · edited May 23 '17 at 11:59

Working from the information in ListAGG in SQLSERVER, I came up with this somewhat ugly example:

with tbl1 as (
  -- Set up initial data set
  select 'Bob' name,        5 amount, 7 ReasonId 
  union all select 'Bob' ,  3,        4
  union all select 'Bob',   2,        1
  union all select 'Brian', 8,        2
  union all select 'Bob',   6,        4
  union all select 'Brian', 1,        3
  union all select 'Tim',   2,        2)
, TBL2 AS ( -- Add a blank to separate the concatenation
  SELECT NAME
       , AMOUNT
       , CAST(ReasonId as varchar) + ' ' ReasonId   from tbl1
)
select ta.name
     , Total
     , ReasonIds from ( 
       (select distinct name, stuff((select distinct '' + t2.ReasonId  from tbl2 t2
where t1.name = t2.name 
for xml path(''), type).value('.','NVARCHAR(MAX)'),1,0,' ') ReasonIds from tbl2 t1) ta
inner join ( select name, sum(amount) Total from tbl1 group by name) tb on ta.name = tb.name) ;

This converts TBL1 to the following:

name   Total ReasonIds
Bob    16    1 4 7 
Brian  9     2 3 
Tim    2     2

Converting rows to columns

3 Answers3