0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for students, teachers, and linguists wanting to discuss the finer points of the Japanese language

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