Convert one rows values to several columns

Question

Here is my query:

select
    t1.id
    ,t1.name
    ,t3.description
    ,t3.amount
from
    table1 (nolock) t1
join 
    table2 (nolock) t2 on t1.t2_id = t2.id
join 
    table3 (nolock) t3 on t2.t3_id = t3.id

Results:

t1.id | t1.name  | t3.description  | t3.amount
------+----------+-----------------+----------
 1    | TEST     | TEST DESC. 1    |   100
 1    | TEST     | TEST DESC. 2    |   200

What I want my results to be:

t1.id | t1.name  | TEST DESC. 1  | TEST DESC. 2
------+----------+---------------+--------------    
  1   | TEST     |    100        |    200

The values in t3.description will not always be the same. There could be many different ones.

I've been trying to look into Pivots but I can't seem to wrap my head around it. The query I provided is a very simplified version of a much larger query but if I can get this to work I should be able to make it work for the larger query.

Thanks everyone for the suggestions.

Set [Bad Habits to kick - putting NOLOCK everywhere](http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/) - it is *not recommended* to use this everywhere - quite the contrary! — marc_s, May 26 '18 at 07:09
Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Enkode, May 26 '18 at 20:59

Rajat Jaiswal · Accepted Answer · 2018-05-26T07:08:32.350

Dynamic Pivot

 DROP TABLE #source
    CREATE  TABLE #source (ID INT, name VARCHAR(100),Description VARCHAR(100), amount int)

INSERT INTO #source( Id,name, description, amount) 
VALUES ( 1              ,'TEST',                'TEST DESC. 1',       100),
 (1    ,    'TEST',          'TEST DESC. 2'     ,      200),
  (1    ,    'TEST',          'TEST DESC. 3'     ,      300)


DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)

SELECT @cols = STUFF
        (
          (
            SELECT ',' + QUOTENAME(Description)
            FROM #source
            GROUP BY Description

            ORDER BY Description
            FOR XML PATH(''), TYPE
          ).value('.', 'NVARCHAR(MAX)'),
          1,1,''
        );

SET @query = 'SELECT *
              FROM
              (
                SELECT Id,name, Description, amount
                FROM #source
             ) x
             PIVOT
             (
                SUM(AMOUNT)
                FOR description IN (' + @cols + ')
             ) p ';

             prINT @qUERY
EXECUTE(@query);

Updating original post. What if I don't know what the names of the values in t3.description will be? — user3007447, May 26 '18 at 06:18
Possible repeat, Check out my answer, You don't need to know column Name: https://stackoverflow.com/a/27532568/1452739 — Enkode, May 26 '18 at 20:58

Convert one rows values to several columns

1 Answers1