July 10, 2018 at 1:46 pm
Hello,
I have a table with 3 columns:
RowNumber integer
xImage Image
xImageDesc nvarchar(30)
I need to flip this into a table with 6 columns where each xImage and xImageDesc from my first table will become 2 columns on my new table.
So basically I need this:
Declare @xImage Table
(
rowNumber int,
xImage char(10),
xImageDesc nvarchar(30)
)
insert into @xImage (RowNumber, xImage, xImageDesc)
select 1,'<image1>','Image 1'
union all
select 2,'<image2>','Image 2'
union all
select 3,'<image3>','Image 3'
union all
select 4,'<image4>','Image 4'
union all
select 5,'<image5>','Image 5'
union all
select 6,'<image6>','Image 6'
RowNumber xImage xImageDesc
1 <image1> Image Desc 1
2 <image2> Image Desc 2
3 <image3> Image Desc 3
4 <image4> Image Desc 4
5 <image5> Image Desc 5
6 <image6> Image Desc 6
to be come this;
RowNumber xImage1 xImageDesc1 xImage2 xImageDesc2 xImage3 xImageDesc3
1 <image1> Image Desc 1 <image2> Image Desc 2 <image3> Image Desc 3
2 <image4> Image Desc 4 <image5> Image Desc 5 <image6> Image Desc 6
I have looked at the PIVOT statement and TBH my 2D mind does not understand how it works 🙂 . I am not even sure it's what I need.Any help would be greatly appreciated.
July 10, 2018 at 2:55 pm
A cross tab will do it nicely.
SELECT
MAX(CASE WHEN rowOrder = 1 THEN xImage END) AS xImage1,
MAX(CASE WHEN rowOrder = 1 THEN xImageDesc END) AS xImageDesc1,
MAX(CASE WHEN rowOrder = 2 THEN xImage END) AS xImage2,
MAX(CASE WHEN rowOrder = 2 THEN xImageDesc END) AS xImageDesc2,
MAX(CASE WHEN rowOrder = 3 THEN xImage END) AS xImage3,
MAX(CASE WHEN rowOrder = 3 THEN xImageDesc END) AS xImageDesc3
FROM (
SELECT *, ROW_NUMBER() OVER(ORDER BY rowNumber) AS rowcounter
FROM @xImage
) AS derived
CROSS APPLY (
SELECT (rowcounter + 2) / 3 AS rowGrouping,
(rowcounter + 2) % 3 + 1 AS rowOrder
) AS alias1
GROUP BY rowGrouping
ORDER BY rowGrouping
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 11, 2018 at 9:45 am
Given that the source data contains an ordering mechanism, using ROW_NUMBER() isn't actually needed here:Declare @xImage AS TABLE (
rowNumber int,
xImage char(10),
xImageDesc nvarchar(30)
);
insert into @xImage (RowNumber, xImage, xImageDesc)
select 1,'<image1>','Image 1' union all
select 2,'<image2>','Image 2' union all
select 3,'<image3>','Image 3' union all
select 4,'<image4>','Image 4' union all
select 5,'<image5>','Image 5' union all
select 6,'<image6>','Image 6';
SELECT RG.rowGrouping,
MAX(CASE WHEN RG.rowOrder = 1 THEN XI.xImage END) AS xImage1,
MAX(CASE WHEN RG.rowOrder = 1 THEN XI.xImageDesc END) AS xImageDesc1,
MAX(CASE WHEN RG.rowOrder = 2 THEN XI.xImage END) AS xImage2,
MAX(CASE WHEN RG.rowOrder = 2 THEN XI.xImageDesc END) AS xImageDesc2,
MAX(CASE WHEN RG.rowOrder = 3 THEN XI.xImage END) AS xImage3,
MAX(CASE WHEN RG.rowOrder = 3 THEN XI.xImageDesc END) AS xImageDesc3
FROM @xImage AS XI
CROSS APPLY (
SELECT
(XI.rowNumber + 2) / 3 AS rowGrouping,
(XI.rowNumber + 2) % 3 + 1 AS rowOrder
) AS RG
GROUP BY RG.rowGrouping
ORDER BY RG.rowGrouping;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 11, 2018 at 9:57 am
sgmunson - Wednesday, July 11, 2018 9:45 AMGiven that the source data contains an ordering mechanism, using ROW_NUMBER() isn't actually needed here:
I wouldn't trust the ordering mechanism as it could contain gaps.
July 11, 2018 at 10:14 am
Luis Cazares - Wednesday, July 11, 2018 9:57 AMsgmunson - Wednesday, July 11, 2018 9:45 AMGiven that the source data contains an ordering mechanism, using ROW_NUMBER() isn't actually needed here:I wouldn't trust the ordering mechanism as it could contain gaps.
Actually it could not. In real life, the rownumber column is not a field, it comes from row_number() over(order by blahblahblah).
I have been very busy and haven't had a chance to the proposed solutions but thanks a lot to both posters for the help, it's greatly appreciated.
July 12, 2018 at 8:55 am
Both options work like a charm.
Thanks again to both of you.
with cte as
(
Select Image, ImageDesc, Sequence, 1 as RecordType
From AQLFrontCartonMarksImages With (NoLock)
Where fKey=128
union all
Select Image, ImageDesc, Sequence, 2 as RecordType
From AQLSideCartonMarksImages With (NoLock)
Where fKey=128
union all
Select Image, ImageDesc, Sequence, 3
From AQLBarcodeLabels With (NoLock)
Where fKey=128
union all
Select Image, ImageDesc, Sequence, 4
From AQLTrackingLabels With (NoLock)
Where fKey=128
union all
Select Image, FileName, Sequence , 5
From ProdDAMGenSampleFront With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 6
From ProdDamGenSampleBack With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence,7
From ProdDAMGenSampleAccessory With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence,8
From ProdDAMGeneralBulkFront With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, Filename, Sequence, 9
From ProdDAMGenBulkBack With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, Filename, Sequence,10
From ProdDAMGeneralBulkAccessory With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 11
From ProdDAMMainCartonMarks With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 12
From ProdDAMSideCartonMarks With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence,13
From ProdDAMInsideCartonView With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 14
From ProdDAMCartonStickers1 With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 15
From ProdDAMCartonStickers2 With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 16
From ProdDAMCustomerStickers1 With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 17
From ProdDAMCustomerStickers2 With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 18
From ProdDAMCustomerStickers3 With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 19
From ProdDAMMainLabel With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 20
From ProdDAMCE With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 21
From ProdDAMCardAndTrackingLabel With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 22
From ProdDAMLicenceSwingTag With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 23
From ProdDAMPriceTag With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 24
From ProdDAMOtherSwingTag With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 25
From ProdDAMHangerAndPolybag With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 26
From ProdDAMFaults1 With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 27
From ProdDAMFaults2 With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 28
From ProdDAMFaults3 With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 29
From ProdDAMFaults4 With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 30
From ProdDAMFaults5 With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 31
From ProdDAMFaults6 With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 32
From ProdDAMFaults7 With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
union all
Select Image, FileName, Sequence, 33
From ProdDAMFaults8 With (NoLock)
Where fKey=128 and DocType in ('jpg', 'jpeg', 'gif', 'tiff', 'bmp', 'png', 'svg', 'Image')
)
Select Image, ImageDesc, RecordType, Sequence, row_number() over(order by RecordType,Sequence) RowNumber
into #1
From cte
Select * from #1
SELECT RG.rowGrouping,
MAX(CASE WHEN RG.rowOrder = 1 THEN XI.Image END) AS Image1,
MAX(CASE WHEN RG.rowOrder = 1 THEN XI.ImageDesc END) AS ImageDesc1,
MAX(CASE WHEN RG.rowOrder = 2 THEN XI.Image END) AS Image2,
MAX(CASE WHEN RG.rowOrder = 2 THEN XI.ImageDesc END) AS ImageDesc2,
MAX(CASE WHEN RG.rowOrder = 3 THEN XI.Image END) AS Image3,
MAX(CASE WHEN RG.rowOrder = 3 THEN XI.ImageDesc END) AS ImageDesc3
FROM #1 AS XI
CROSS APPLY (
SELECT
(XI.rowNumber + 2) / 3 AS rowGrouping,
(XI.rowNumber + 2) % 3 + 1 AS rowOrder
) AS RG
GROUP BY RG.rowGrouping
ORDER BY RG.rowGrouping;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply