these are my data:
and I want to have this result:
Please note that the values inserted into column "App", haven't got the same value or the same quantity, so once I could find 2 rows and another time 10 rows
2
- I tried with your example, but if I use it, I have to specify all values that "app" can be store, and in the future, they will be more than 1000img.simone– img.simone2015-04-23 08:40:43 +00:00Commented Apr 23, 2015 at 8:40
- Thank you for "SQL Server dynami PIVOT". Can you help me with the code?img.simone– img.simone2015-04-23 08:42:49 +00:00Commented Apr 23, 2015 at 8:42
Add a comment |
1 Answer
You can use a temp table to store your app values, then concat them into a variable. Finally using T-SQL you can construct your dynamic pivot:
CREATE TABLE #VALS (VALS NVARCHAR(MAX)) INSERT INTO #VALS SELECT DISTINCT APP FROM [TABLE1] DECLARE @SQL NVARCHAR(MAX) DECLARE @VALS NVARCHAR(MAX) SELECT @VALS = COALESCE(@VALS+', ','') + '[' + VALS + ']' FROM #VALS SET @SQL = ' SELECT NAME, '+@VALS+' FROM [TABLE1] PIVOT (MAX([VERSION]) FOR APP IN ('+@VALS+')) PIV' PRINT @SQL EXEC (@SQL) 1 Comment
John Bell
Just a note for this. This will only work as expected if your APP column is distinct. Otherwise it will try to add your version numbers together... EDIT: I'm paranoid, so I've changed the pivot clause from SUM to MAX, but this will still not work as you think if your APP values are not distinct.