Pivoting a 2 columns table to a 6 columns table with 1/3 the rows

• Eric_Gagne

  SSCrazy

  Points: 2062

  More actions

  July 10, 2018 at 1:46 pm

  #407169

  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.

• ScottPletcher

  SSC Guru

  Points: 101126

  More actions

  July 10, 2018 at 2:55 pm

  #1997074

  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".

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  July 11, 2018 at 9:45 am

  #1997150

  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)

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 11, 2018 at 9:57 am

  #1997153

  sgmunson - Wednesday, July 11, 2018 9:45 AM

  Given 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.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Eric_Gagne

  SSCrazy

  Points: 2062

  More actions

  July 11, 2018 at 10:14 am

  #1997155

  Luis Cazares - Wednesday, July 11, 2018 9:57 AM

  sgmunson - Wednesday, July 11, 2018 9:45 AM

  Given 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.

• Eric_Gagne

  SSCrazy

  Points: 2062

  More actions

  July 12, 2018 at 8:55 am

  #1997314

  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;