How to toogle datas according to an attribute

  • I have a table structured as following :

    ATTR LIB

    1row01

    2row02

    3row03

    1row11

    2row12

    3row13

    1row21

    2row22

    3row23

    and I want to transform the structure to build a view as :

    COL1 COL2 COL3

    row01 row02 row03

    row11 row12 row13

    row21 row22 row 23

    I do not seen how to write the script to perform the transformation. Thanks for all helps.

  • Not really sure how this is going to help you, but based on what you posted, here is an answer as an attachment. Again, I seem to be having issues with code that I try to cut and paste.

  • Hi

    Just another possible solution

    DECLARE @t TABLE (Attr INT, Lib VARCHAR(10))

    INSERT INTO @t

    SELECT 1, 'row01'

    UNION ALL SELECT 2, 'row02'

    UNION ALL SELECT 3, 'row03'

    UNION ALL SELECT 1, 'row11'

    UNION ALL SELECT 2, 'row12'

    UNION ALL SELECT 3, 'row13'

    UNION ALL SELECT 1, 'row21'

    UNION ALL SELECT 2, 'row22'

    UNION ALL SELECT 3, 'row23'

    ; WITH

    cte (Attr, Lib, RowNum) AS

    (

    SELECT

    Attr,

    Lib,

    ROW_NUMBER() OVER (PARTITION BY Attr ORDER BY (SELECT 1))

    FROM @t

    )

    SELECT

    a1.Lib,

    a2.Lib,

    a3.Lib

    FROM cte a1

    JOIN cte a2 ON a1.RowNum = a2.RowNum

    JOIN cte a3 ON a1.RowNum = a3.RowNum

    WHERE

    a1.Attr = 1

    AND a2.Attr = 2

    AND a3.Attr = 3

    But I think Lynn's approach should perform better 😀

  • Thanks for yours quicky replies, but I have made a mistake in my question.

    I would obtain eventually:

    COL1 COL2 COL3

    row01 row11 row21

    row02 row12 row22

    row03 row13 row23

  • Here is my suggestion at this point, take the code you have been provided, and see if you can make the necessary changes to meet your new requirements. If so, post the code you came up with to solve the problem. If not, post the code you came up but isn't solving the problem, and show us what it is doing so we may be able to help you fix it (or come up with something total different).

  • Agreed, I've been building an example based off of Lynn's code for the past 15 minutes, and have already come up with like 9 other ways of doing this, similar to the partition by approach (minus CTE) above.

    I'm perplexed how this would even be useful. Without more context the best approach is the text-based "GROUP BY LEFT(Lib, 4)" cross-tab approach, which is extremely hard-coded but the only answer that makes sense based off of the limited amount of "purpose" in the question.

  • Thanks for all yours helps.

    Finally, I use the Florian Reischl's script as base. For my database, the Lynn Pettis's one is not adapted because of the group by instruction : row01 was only an example and my datas are not too structured.

    CREATE TABLE [dbo].[TEST](

    [ID] [int] NULL,

    [NUM_COLONNE] [int] NULL,

    [LIBELLE] [varchar](50) NULL

    ) ON [PRIMARY]

    WITH CT_TRANSPOSE(ID, NUM_COLONNE, LIBELLE, ROWNUM) AS

    (

    SELECT

    ID,

    NUM_COLONNE,

    LIBELLE,

    ROW_NUMBER() OVER (PARTITION BY NUM_COLONNE ORDER BY (ID))

    FROM TEST

    )

    SELECT

    A1.ID,

    A1.LIBELLE AS COL_01,

    A2.LIBELLE AS COL_02,

    A3.LIBELLE AS COL_03,

    A4.LIBELLE AS COL_04,

    A5.LIBELLE AS COL_05,

    A6.LIBELLE AS COL_06

    FROM

    CT_TRANSPOSE A1

    JOIN CT_TRANSPOSE A2 ON A1.ROWNUM = A2.ROWNUM

    JOIN CT_TRANSPOSE A3 ON A1.ROWNUM = A3.ROWNUM

    JOIN CT_TRANSPOSE A4 ON A1.ROWNUM = A4.ROWNUM

    JOIN CT_TRANSPOSE A5 ON A1.ROWNUM = A5.ROWNUM

    JOIN CT_TRANSPOSE A6 ON A1.ROWNUM = A6.ROWNUM

    WHERE

    A1.NUM_COLONNE = 1

    AND A2.NUM_COLONNE = 2

    AND A3.NUM_COLONNE = 3

    AND A4.NUM_COLONNE = 4

    AND A5.NUM_COLONNE = 5

    AND A6.NUM_COLONNE = 6

    ID COL_01 COL_02 COL_03 COL_04 COL_05 COL_06

    1row01row02row03row04row05row06

    7row11row12row13row14row15row16

    13row21row22row23row24row25row26

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply