3

these are my data:

enter image description here

and I want to have this result:

enter image description here

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

img.simone
  • 632
  • 6
  • 10
  • 23
  • 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 1000 – img.simone Apr 23 '15 at 08:40
  • Thank you for "SQL Server dynami PIVOT". Can you help me with the code? – img.simone Apr 23 '15 at 08:42

1 Answers1

3

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)
John Bell
  • 2,350
  • 1
  • 14
  • 23
  • 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. – John Bell Apr 23 '15 at 09:02