0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for programming puzzle enthusiasts and code golfers

CREATE table #yourtable
    ([Id] int,[Group] int, [Value] varchar(6), [ColumnName] varchar(13))
;
    
INSERT INTO #yourtable
    ([Id],[Group], [Value], [ColumnName])
VALUES
    (1,1, 'John', 'FirstName'),
    (2,1, '2.4', 'Amount'),
    (3,1, 'ZH1E4A', 'PostalCode'),
    (4,1, 'Fork', 'LastName'),
    (5,1, '857685', 'AccountNumber'),
    (6,2, 'Pedro', 'FirstName'),
    (7,2, '5.1', 'Amount'),
    (8,2, '123456', 'PostalCode'),
    (9,2, 'Torres', 'LastName'),
    (10,2, '857686', 'AccountNumber')
;
;



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

select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName) 
                    from #yourtable
                    group by [Group], ColumnName, id
                    having [group] = (SELECT TOP 1 MIN([Group])FROM #yourtable)
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


        

set @query = N'SELECT ' + @cols + N' from 
             (
                select value, ColumnName,[Group]
                from #yourtable
                GROUP BY [Group],ColumnName,Value
            ) x
            pivot 
            (
                max(value)
                for ColumnName in (' + @cols + N')
            ) p '

select @query;

exec sp_executesql @query;

fork query

edited aug 9 anonymous

created jun 8 16 bluefeet