SQL Add columns based on value from another column-CodePudding

I have a table like this:

id	cols	val
1	date	01-01-01
1	name	abc
1	flag	True
1	end_date	null
2	date	01-01-02
2	name	abcd
2	flag	False
2	end_date	01-01-03

And I need to create a table that looks like

id	date	name	flag	end_date
1	01-01-01	abc	True	null
2	01-01-02	abcd	False	01-01-03

I can use select/with only. No functions or create/update

Thanks for your help

CodePudding user response：

An interesting technique to remember is that you can use MAX(CASE WHEN... on text values to achieve results like this:

SELECT id
, MAX(CASE WHEN cols = 'date' THEN val ELSE NULL END) AS dt
, MAX(CASE WHEN cols = 'name' THEN val ELSE NULL END) AS nm
, MAX(CASE WHEN cols = 'flag' THEN val ELSE NULL END) AS flag
, MAX(CASE WHEN cols = 'end_date' THEN val ELSE NULL END) AS end_date
FROM #t
GROUP BY id

I've tried to avoid using SQL keywords as column names, so you have dt and nm instead of date and name.

You will probably want to apply some type casting as well to get it into a more usable format.

dbfiddle.uk

CodePudding user response：

You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output.

https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

CodePudding user response：

U can use this query pivot it table value

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

SET @cols = STUFF((SELECT distinct ','   QUOTENAME(c.cols) 
            FROM yourtablename c
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Id, '   @cols   ' from 
            (
                select id
                    ,val
                from yourtablename
           ) x
            pivot 
            (
                 val
                for cols in ('   @cols   ')
            ) p '


execute(@query);