Hello...
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Table1](
	[MYId] uniqueidentifier NOT NULL,
	[Name] [varchar](50) NULL,
CONSTRAINT [PK_Table1_MYId] PRIMARY KEY CLUSTERED 
(
	[MYId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING ON
GO

INSERT INTO [AdventureWorks2008R2].[dbo].[Table1]([MYId],[Name])
VALUES(NEWID(),'Black') GO

CREATE TABLE [dbo].[Table2](
	[ThisId] [uniqueidentifier] NOT NULL,
	[MYId] [uniqueidentifier] NOT NULL,
	[Name] [varchar](50) NULL,
	[Image] Image null
	PRIMARY KEY (ThisId),
FOREIGN KEY (MYId) REFERENCES Table1(MYId))
GO

INSERT INTO Table2 ([ThisId],[MYId],[Name], Image)
SELECT NEWID(), MYId, '1', (SELECT * FROM OPENROWSET(BULK N'C:\Today.jpg', SINGLE_BLOB)jpg) From Table1

INSERT INTO Table2 ([ThisId],[MYId],[Name], Image)
SELECT NEWID(), MYId, '2', (SELECT * FROM OPENROWSET(BULK N'C:\Today.jpg', SINGLE_BLOB)jpg) From Table1

INSERT INTO Table2 ([ThisId],[MYId],[Name], Image)
SELECT NEWID(), MYId, '3', (SELECT * FROM OPENROWSET(BULK N'C:\Today.jpg', SINGLE_BLOB)jpg) From Table1

Select * from Table2 where MYId = '3FAF509E-BC5C-48EE-A02F-04398D4B94A2'

1. How do I return the set so it shows the 1 MYId but the three Name's and Image hex files ?

Thanks Richard

I don't understand the question. Isn't the result set you are producing doing exactly what you are asking for?

I don't understand the question. Isn't the result set you are producing doing exactly what you are asking for?

To make it easier to see the problem I would suggest that you display the exact results that you want.  Just use cut and paste in Word or Excel (you can reduce the binary to the first few significant characters) to display the expected results.

Try the following (I don't have SQL Server running so I can't test it):

SELECT *
FROM (
SELECT [MYId],[Name], Image
from TABLE2
) x
unpivot (max(Image) for [Name] in ([1],[2],[3]))
unp

This might now work with the image data type (or even varbinary(max)).

I would suggest that you do the unpivoting in the client.

Another alternative.

with 
s as 
(
select distinct MyID
from Table2 
)
s1 as (
SELECT MyID, [Image] from Table2
where [Name] = 1
),
 s2 as (
SELECT MyID, [Image] from Table2
where [Name] = 2
),
 s3 as (
SELECT MyID, [Image] from Table2
where [Name] = 3
),
  s4 as (
SELECT MyID, [Image] from Table2
where [Name] = 4
) 
select s.MyID
, s1.[Image] as Image1
, s2.[Image] as Image2
, s3.[Image] as Image3
, s4.[Image] as Image4
from s
left outer join s1 on s.MyID = s1.MyID
left outer join s2 on s.MyID = s2.MyID
left outer join s3 on s.MyID = s3.MyID
left outer join s4 on s.MyID = s4.MyID;

To make it easier to see the problem I would suggest that you display the exact results that you

In BIDS the final result will look like this..

Inherently there will be multiple name's and associated image's maybe 1 or maybe 3 some maybe null...

I don't know what operator could be used for the operand image. So are pivot falls over.

Q1. Moving forward, What operator can be used for the image operand ? It is image operand and it can't be changed to varbinary what aggregate function be used in this case ?

Q2. I would suggest that you do the unpivoting in the client. Can you further explain please ?

SELECT *
FROM (
SELECT [MYId],[Name], Image from TABLE2
) x
pivot (max([Image])
            for [Name] in ([1],[2],[3]))

The second query returns the correct result! But

with 
s as 
(
select distinct MyID
from Table2 
)
s1 as (
SELECT MyID, [Image] from Table2
where [Name] = 1
),
 s2 as (
SELECT MyID, [Image] from Table2
where [Name] = 2
),
 s3 as (
SELECT MyID, [Image] from Table2
where [Name] = 3
),
  s4 as (
SELECT MyID, [Image] from Table2
where [Name] = 4
) 
select s.MyID
, s1.[Image] as Image1
, s2.[Image] as Image2
, s3.[Image] as Image3
, s4.[Image] as Image4
from s
left outer join s1 on s.MyID = s1.MyID
left outer join s2 on s.MyID = s2.MyID
left outer join s3 on s.MyID = s3.MyID
left outer join s4 on s.MyID = s4.MyID;

Q3. If there are multiple MyId's and not one distinct that may or may not have image's associated to them could a parameter be passed to match the records and logically return each record just generating new columns for image's (See image) a new s in this query would have to be added to the CTE? Or is it worth looping through the returning records ??

Once again thanks for the help..

RichardO

In BIDS the final result will look like this..

<https://social.msdn.microsoft.com/Forums/getfile/711689>

But there's no MyID at all?

Q3. If there are multiple MyId's and not one distinct that may or may not have image's associated to them could a parameter be passed to match the records and logically return each record just generating new columns for image's (See image) a new s in this query would have to be added to the CTE? Or is it worth looping through the returning records ??

I'm still at odds to grasp what you are looking for. But maybe that is due to that I am brainwashed from the model that a relational database engine works from. To wit, a SELECT statment returns a table, and table is supposed to model a entity with a set of distinct attributes. Particularly, the attributes are static. So a SELECT statement cannot generate a different number of columns depending on the data. If you know that there are never more than N images per id, you can write a pivot query with N image columns. You can also generate the query dynamically, but this increases the complexity of the code considerably.

However, that still begs the question: why? Often it is better to this type of pivoting operations in the client.

In BIDS the final result will look like this.

Human error on my part.

There is normally only one image per id. But in some case there could be more than one..

Logically moving forward I feel a the pivot would work so may a Matrix in SSRS. But as I test I would like to work on both the sp and the client

The return I would like to achieve is, which work from the alternative.

As I workaround this old system I -fight sometimes to grasp certain logic that has been applied in there.

Q1.

SELECT *
FROM (
SELECT [MYId],[Name], Image from TABLE2
) x
pivot --(MAX([Image])
        (max([Image])
            for [Name] in ([1],[2],[3]))

The operand image is of type Image - not varbinary what operator can be applied in this case to return the desired results ?

A Matrix report is probably the best way to achieve this, yes. (He said, despite that he does not know SSRS himself.)

To run the pivot query, you would need to use convert(varbinary(MAX), image). Or even better - change the data type in the database. The image data type has been deprecated for soon ten years now.

1. I would alter it right now but  I'd need permission, I'd have to approach them and add it to the roadmap... (The query is me testing and asking).

2. I'll learn the Matrix in SSRS half a day's work I think.. Maybe already be in it  !!! I'm thinking about the compiler/object/class  structure (C, C#, VB or F#) now that runs the Matrix in SSRS.. hummm Anyway...

SELECT *
FROM
(SELECT [Name], Image from TABLE2) AS SourceTable
PIVOT
(
  convert(varbinary(MAX), image) 
         for [Name] in ([1],[2],[3])))
) AS PivotTable;

Syntax looks correct - https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

But Its returning

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'convert'.

What have I missed here  and how do I take nulls into consideration when using aggregate functions?

Thank you... :D

• Edited by Richard I Evans 8 hours 36 minutes ago

1. I would alter it right now but  I'd need permission, I'd have to approach them and add it to the roadmap... (The query is me testing and asking).

2. I'll learn the Matrix in SSRS half a day's work I think.. Maybe already be in it  !!! I'm thinking about the compiler/object/class  structure (C, C#, VB or F#) now that runs the Matrix in SSRS.. hummm Anyway...

SELECT *
FROM
(SELECT [Name], Image from TABLE2) AS SourceTable
PIVOT
(
  convert(varbinary(MAX), image) 
         for [Name] in ([1],[2],[3])))
) AS PivotTable;

Syntax looks correct - https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

But Its returning

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'convert'.

What have I missed here  and how do I take nulls into consideration when using aggregate functions?

Thank you... :D

• Edited by Richard I Evans Saturday, August 22, 2015 10:31 PM

Move converting inside the sub-select, e.g.

SELECT *
FROM
(SELECT [Name], row_number() over (partition by Name order by null) as Rn, convert(varbinary(max),Image) as Image  from TABLE2) AS SourceTable
PIVOT
(
  max(image)
         for [Rn] in ([1],[2],[3])))
) AS PivotTable;

SELECT *
FROM
(SELECT [Name], 
         convert(varbinary(max),Image) as Image  
         from TABLE2) AS SourceTable
PIVOT
(
  max(image)
         for [Name] in ([1],[2],[3])
) AS PivotTable;

• Marked as answer by Richard I Evans 23 hours 15 minutes ago
• Unmarked as answer by Naomi NModerator 23 hours 10 minutes ago