DECLARE @Foo TABLE (
	[ID] INT
	, [key] NVARCHAR(5)
	, [value] INT
	)

INSERT @foo ([Id], [key], [value])
      SELECT 1, 'A', 10
UNION SELECT 1, 'B', 20
UNION SELECT 1, 'C', 50
UNION SELECT 2, 'A', 10
UNION SELECT 4, 'D', 100

SELECT * FROM @foo

-- With implicit 0 if no data (note this will be *WRONG* if you have negative integers in results)
SELECT
	f.[Id]
	, MAX(CASE WHEN f.[key] = 'A' THEN f.[value] ELSE 0 END) AS [A]
	, MAX(CASE WHEN f.[key] = 'B' THEN f.[value] ELSE 0 END) AS [B]
	, MAX(CASE WHEN f.[key] = 'C' THEN f.[value] ELSE 0 END) AS [C]
	, MAX(CASE WHEN f.[key] = 'D' THEN f.[value] ELSE 0 END) AS [D]
FROM
	@foo f
GROUP BY
	f.[ID]

-- Treat no data as NULL
SELECT
	f.[Id]
	, MAX(CASE WHEN f.[key] = 'A' THEN f.[value] END) AS [A]
	, MAX(CASE WHEN f.[key] = 'B' THEN f.[value] END) AS [B]
	, MAX(CASE WHEN f.[key] = 'C' THEN f.[value] END) AS [C]
	, MAX(CASE WHEN f.[key] = 'D' THEN f.[value] END) AS [D]
FROM
	@foo f
GROUP BY
	f.[ID]

quote:

---

Originally posted by millaine

Hi all,

I've written a pivot query and the results I get are something like this -

Column1 Column2 Column3 Column4 Column5
10 NULL NULL NULL NULL
NULL 20 NULL NULL NULL
NULL NULL 30 NULL NULL
NULL NULL NULL 40 NULL

Is it possible to modify the query so that it returns just one row with 10 under column1, 20 under column2, 30 under column3 and 40 under column4?

I'd really appreciate help on this...

Thanks in anticipation.

Millaine.

---

IF OBJECT_ID('tempdb..#foo') IS NOT NULL DROP TABLE #foo
DECLARE @sql NVARCHAR(MAX)

CREATE TABLE #Foo (
	[ID] INT
	, [staffId] INT
	, [name] NVARCHAR(50)
	, [amount] INT
	)

INSERT #foo ([Id], [staffId], [name], [amount])
      SELECT 1, 1, 'value1', 10
UNION SELECT 2, 1, 'value2', 20
UNION SELECT 3, 1, 'value3', 50
UNION SELECT 4, 1, 'value4', 10
UNION SELECT 5, 2, 'value1', 100
UNION SELECT 6, 2, 'value3', 10
UNION SELECT 7, 2, 'FOOO', 45
UNION SELECT 7, 2, 'BAR', 123

SELECT * FROM #foo

-- Dyanmic Pivot with column Names
SET @sql = N'
SELECT
	[staffID]'

SELECT @sql = @sql + N'
	, MAX(CASE WHEN [name] = ''' + [name] + ''' THEN [amount] ELSE NULL END) AS [' + [name] + ']'
FROM
	( SELECT DISTINCT [name] FROM #foo ) f

SET @sql = @sql + N'
FROM
	#foo
GROUP BY
	[staffId]'

EXEC sp_executeSql @sql

staffID     BAR         FOOO        value1      value2      value3      value4
----------- ----------- ----------- ----------- ----------- ----------- -----------
1           NULL        NULL        10          20          50          10
2           123         45          100         NULL        10          NULL