Pivot works, but spread column values not as expected

realJSOP

I have a table with the following schema:

DECLARE @images TABLE
(
[Id] BIGINT NOT NULL,
[UserId] NVARCHAR(450) NOT NULL,
[VehID] BIGINT NOT NULL,
[VehImage] VARBINARY(MAX) NULL
);

There are no exactly 10 rows per user. Currently, all VehImage columns are null (intentionally). Here is my sql query (@images is a table variable that I pre-populate with the desired records):

SELECT UserId,VehID
,[Image1],[Image2],[Image3],[Image4],[Image5]
,[Image6],[Image7],[Image8],[Image9],[Image10]
FROM (
SELECT UserId
,VehID
,VehImage
FROM @images
) AS SourceTable
PIVOT (
Count(VehImage)
FOR VehImage IN ([Image1],[Image2],[Image3]
,[Image4],[Image5],[Image6]
,[Image7],[Image8],[Image9]
,[Image10])
) AS PivotTable;

I have the pivot working, in that I get exactly one row back with the expected UserId, and VehId, and 10 image columns. My problem is that the Image columns all have the value 0 instead of the expected null. What am I doing that would cause that? EDIT ---------------------------------------- I changed the aggregate function from count to max, and all of the images are now showing null as expected. I don't know yet if that's the ultimate solution. EDIT #2 ------------------------------------- Nope, I plugged a fake value into the first image record, and all of the image columns are still coming back as null...

".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013

Richard Deeming

#realJSOP wrote:

Currently, all VehImage columns are null (intentionally). ...

FOR VehImage IN (...)

If all of the VehImage values are Null, they won't match the IN filter. And it seems odd to be pivoting on the same column you're aggregating. What are you actually trying to do?

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

realJSOP

i want to return a row that has all of the users images in it.

".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013

Richard Deeming

Maybe something like this?

WITH cte As
(
SELECT
UserId,
VehId,
VehImage,
ROW_NUMBER() OVER (PARTITION BY UserId, VehId ORDER BY Id) As RN
FROM
@images
)
SELECT
UserId,
VehId,
[1] As Image1,
[2] As Image2,
[3] As Image3,
[4] As Image4,
[5] As Image5,
[6] As Image6,
[7] As Image7,
[8] As Image8,
[9] As Image9,
[10] As Image10
FROM
cte As SourceTable
PIVOT
(
Max(VehImage)
FOR RN IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])
) As PivotTable
;

Or without the pivot:

WITH cte As
(
SELECT
UserId,
VehId,
VehImage,
ROW_NUMBER() OVER (PARTITION BY UserId, VehId ORDER BY Id) As RN
FROM
@images
)
SELECT
UserId,
VehId,
MAX(CASE RN WHEN 1 THEN VehImage END) As Image1,
MAX(CASE RN WHEN 2 THEN VehImage END) As Image2,
MAX(CASE RN WHEN 3 THEN VehImage END) As Image3,
MAX(CASE RN WHEN 4 THEN VehImage END) As Image4,
MAX(CASE RN WHEN 5 THEN VehImage END) As Image5,
MAX(CASE RN WHEN 6 THEN VehImage END) As Image6,
MAX(CASE RN WHEN 7 THEN VehImage END) As Image7,
MAX(CASE RN WHEN 8 THEN VehImage END) As Image8,
MAX(CASE RN WHEN 9 THEN VehImage END) As Image9,
MAX(CASE RN WHEN 10 THEN VehImage END) As Image10
FROM
cte As SourceTable
GROUP BY
UserId,
VehId
;

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

realJSOP

They both worked great, but I went with the non-pivot version. Thanks, man.

".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013