How to pivot a table in SQL Server

Question

I have a table like this

And I want result like this

I tried pivoting but could not get this result. Because there can be more than four values in first table.

NOTE: this is question is quite different from the mentioned above by some one. The answer in the similar question shows another result set, while I have attached images to avoid such stupid confusion.

There are no mind-readers here. So if you want to be pointed to the errors you've made in your query - you have to show your query first. — Andrey Korneyev, Jul 01 '16 at 12:29
Hmm, can you provide more details on what is your table structure and how you want to pivot that ? — M22an, Jul 01 '16 at 12:30
The above images speaks clearly about the structure of the table. And the second table also indicates what result I want. — Lali, Jul 01 '16 at 12:32
have you tried this?: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx Or do you not know all the "Title" values you have if your original table? — Alex_404, Jul 01 '16 at 12:34
@Alex_404, in the given link, pivot query mentions columns explicitly like "FOR DaysToManufacture IN ([0], [1], [2], [3], [4])", In my case 0, 1, 2, 3, and 4 can be dynamic. Means number of columns can vary. — Lali, Jul 01 '16 at 12:43
Calling a duplicate flag "stupid confusion" is not likely to ingratiate you to other people who might want to help. Think about it: If you take the principles shown in the other answer, and just remove one column from what it's doing, that will answer your question. — underscore_d, Jul 01 '16 at 13:09
Downgrading to some one question and flagging question as duplicate, merely without check what I want in my answer is not appropriate. — Lali, Jul 01 '16 at 13:13

score 0 · Answer 1 · answered Jul 01 '16 at 12:53

As mentioned in comments, this is a duplicate of dynamic pivot question. However, I just did something like this few days ago and still have the query.

Replace < table > with your table name:

declare @sql nvarchar(max) = ''
SELECT @sql = @sql+SUBSTRING(t.cols, 0,LEN(t.cols))
FROM (SELECT stuff((SELECT DISTINCT '['+a.Title+'],'
                FROM (SELECT DISTINCT Title FROM <table>) a
                FOR XML PATH('')),1,0,'') AS cols
        ) AS t
SET @sql = 'SELECT '+@sql+CHAR(10)+
          +' FROM (SELECT Title,Value'+CHAR(10)+
          +'       FROM <table>) AS src'+CHAR(10)+
          +' PIVOT'+CHAR(10)+
          +' (max(Value) FOR Title IN ('+@sql+')) AS pvt'
exec(@sql)

How to pivot a table in SQL Server

1 Answers1