Dynamically convert column to rows and then join with other table

Question

I have two tables that i like to combine together, the first are just an normal table. But the second the structure i having the problem with. I need to convert the rows in the column [Name] to columns. The two problem im having is that the picture only shows data from one id, and i need to convert distinct list of columns and then display the [value] column as rows. Second issue is to do all this while still joining the main table together with the table in the picture. I know that pivot exists and i have tried it. But can't get it to work, just with the main table. Does anybody have an idea to have to solve this issue?? Here is my unsuccessful attempt.

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

select @cols = STUFF((SELECT ',' + QUOTENAME([Name]) 
                    from [dbo].[TfsItem]
                    group by [Name]
                    order by [Name]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

        SELECT @cols
set @query = N'SELECT ' + @cols + N' from 
             (
                select value, [Name]
                from [dbo].[TfsItem]
            ) x
            pivot 
            (
                max(value)
                for [Name] in (' + @cols + N')
            ) p '

exec sp_executesql @query;

Try changing your pivot to use `max([Name])` instead of `max([value])`. — S.C., May 20 '18 at 20:27
@S.C. I just tried it and it just converts it wrong and that sense that it just inserts a lot of null in the columns even if i know that it's not empty. — mortenstarck, May 20 '18 at 20:29
You are not selecting anything else, e.g. workitemid: select workitemid, value, [Name] from [dbo].[TfsItem] — Ako, May 20 '18 at 20:40
@Ako, i have actually tried it but the performance are bad, so i actually dont get an result to verify, because im stopping it after 15 min — mortenstarck, May 20 '18 at 20:49
Maybe it helps not to select all the columns and all items, you can try by selecting top 10 rows. — Ako, May 20 '18 at 20:54
You need to make sure that "ItemID" is in your final select. See: https://stackoverflow.com/q/15183160/6305294 — Alex, May 20 '18 at 22:03

Dynamically convert column to rows and then join with other table

0 Answers0