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

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

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

  • 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)

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

  • 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