Transform SQL Query Results

Question

I need to take some query results and flatten them out for a report.

DECLARE @randomTable table (ID int, OtherID int, Val varchar(max))

insert into @randomTable(ID, OtherID, Val)
values (1, 100, 'Some Value 1'), (2, 100, 'Some Other 2'),
       (3, 100, 'Some Value 3'), (4, 200, 'Some Other 4'),
       (5, 200, 'Some Value 5'), (6, 300, 'Some Other 6'),
       (7, 300, 'Some Value 7'), (8, 300, 'Some Other 8'),
       (9, 400, 'Some Value 9'), (10, 500, 'Some Other 10')            

select OtherID, Val from @randomTable

results:

--  100 | Some Value 1
--  100 | Some Other 2
--  100 | Some Value 3
--  200 | Some Other 4
--  200 | Some Value 5
--  300 | Some Other 6
--  300 | Some Value 7
--  300 | Some Other 8
--  400 | Some Value 9
--  500 | Some Other 10

Is there an SQL way to change this to select as:

--  100 | Some Value 1 | Some Other 2 | Some Value 3
--  200 | Some Other 4 | Some Value 5
--  300 | Some Other 6 | Some Value 7 | Some Other 8
--  400 | Some Value 9
--  500 | Some Other 10

NOTE: The above is an example. My real data is not static. Also, in my real data the OtherID is a string value and Val values are images stored as varbinary.

I would of course have to limit how many columns I am going to allow. I am thinking a max of 5 (after that I am fine to lose the extra rows).

Is there any way to do this?

Answer 1

;WITH cte
     AS (SELECT OtherID,
                Val,
                ROW_NUMBER() OVER (PARTITION BY OtherID ORDER BY (SELECT 0)) rn
         FROM   @randomTable)
SELECT OtherID,
       [1],
       [2],
       [3],
       [4],
       [5]
FROM   cte PIVOT( MAX(Val) FOR rn IN ([1], [2], [3], [4], [5])) AS PivotTable;  

To provide some explanation SELECT * FROM cte would return the following.

OtherID     Val                  rn
----------- -------------------- ---
100         Some Value 1         1
100         Some Other 2         2
100         Some Value 3         3
200         Some Other 4         1
200         Some Value 5         2
300         Some Other 6         1
300         Some Value 7         2
300         Some Other 8         3
400         Some Value 9         1
500         Some Other 10        1

I have added the row_number column to give something to use in the PIVOT. Notice that there is zero or one row per OtherID,rn combination. The PIVOT statement is equivalent to the following.

SELECT OtherID,
       MAX(CASE WHEN rn=1 THEN Val END) AS [1],
       MAX(CASE WHEN rn=2 THEN Val END) AS [2],
       MAX(CASE WHEN rn=3 THEN Val END) AS [3],
       MAX(CASE WHEN rn=4 THEN Val END) AS [4],
       MAX(CASE WHEN rn=5 THEN Val END) AS [5]
FROM cte
GROUP BY OtherID /*You want one row per OtherID */

Answer 2

Im not sure if you want to pivot into columns or concatenate results into a single column (for reporting).

If you're looking for a dynamic pivot, please review the article here: link and subsequent comments there. If you are able to limit the columns involved in the pivot you can use the pivot operator (as mentioned already) and get much better performance.

If you're wishing to concatenate rows to columns please review the below query. Leveraging XML PATH does have some implications with special characters (<& etc.), so if this applies to your situation please post back and we can extend this simple example.

 -- if you have normalized type table use it instead of this cte
    ;with   c_distinct (OtherId) 
    as      (   select  distinct OtherId 
                from    @randomTable
            )
    select  OtherId,
            stuff(c, 1, 2, '') 
    from    c_distinct cd
    cross
    apply   (   select  '| ' + Val
                from    @randomTable rt
                where   cd.OtherId = rt.OtherId
                for xml path('')
            ) d(c)

Answer 3

What you are asking for is a denormalization, right?

I think your question is answered here: Denormalizing Data (Maybe A Pivot?)