Rows horizontally, no PIVOT

  • Lisset

    SSCommitted

    Points: 1858

    Hi everybody I have the follow table CLI_NU:

    DOC DDN TEL

    00065955 NULL 999434109

    00065955 01 2952230

    00065955 01 6171700

    00091252 NULL 3460852

    00091252 NULL 6108000

    And this data have to show of the follow way:

    DOC DDN1 TEL1 DDN2 TEL2 DDN3 TEL3

    00065955 NULL 999434109 01 2952230 01 6171700

    00091252 NULL 3460852 NULL 6108000 NULL NULL

    With the command PIVOT I can't do it, I has try.

    Please help me.

    Thanks a lot

  • kramaswamy

    SSCoach

    Points: 18135

    Are there always going to be at most three values for each DOC? If so, you can do this:

    CREATE TABLE #CLI_NU

    (

    DOC VARCHAR(8),

    DDN VARCHAR(2),

    TEL VARCHAR(9)

    )

    INSERT INTO #CLI_NU (DOC, DDN, TEL)

    VALUES ('00065955', NULL, '999434109')

    INSERT INTO #CLI_NU (DOC, DDN, TEL)

    VALUES ('00065955', '01', '2952230')

    INSERT INTO #CLI_NU (DOC, DDN, TEL)

    VALUES ('00065955', '01', '6171700')

    INSERT INTO #CLI_NU (DOC, DDN, TEL)

    VALUES ('00091252', NULL, '3460852')

    INSERT INTO #CLI_NU (DOC, DDN, TEL)

    VALUES ('00091252', NULL, '6108000')

    WITH cte AS

    (

    SELECT DOC, DDN, TEL, ROW_NUMBER() OVER (PARTITION BY DOC ORDER BY DDN) AS rowNum

    FROM #CLI_NU

    )

    SELECT

    DOC,

    (SELECT DDN FROM cte c2 WHERE c1.DOC = c2.DOC AND rowNum = 1) AS DDN1,

    (SELECT TEL FROM cte c2 WHERE c1.DOC = c2.DOC AND rowNum = 1) AS TEL1,

    (SELECT DDN FROM cte c2 WHERE c1.DOC = c2.DOC AND rowNum = 2) AS DDN2,

    (SELECT TEL FROM cte c2 WHERE c1.DOC = c2.DOC AND rowNum = 2) AS TEL2,

    (SELECT DDN FROM cte c2 WHERE c1.DOC = c2.DOC AND rowNum = 3) AS DDN3,

    (SELECT TEL FROM cte c2 WHERE c1.DOC = c2.DOC AND rowNum = 3) AS TEL3

    FROM cte c1

    GROUP BY DOC

    Of course, this is not very scalable. If you start getting large tables, having multiple queries against the same table will slow things down considerably. In that case you should probably reconsider the purpose and nature of this query.

  • Lisset

    SSCommitted

    Points: 1858

    Mi table have less of 3000 rows, your query is efficient.

    Thanks by your help! You has safe me!

  • LutzM

    SSC Guru

    Points: 107049

    Here's an alternative that should "slightly" outperform the previous solution...

    It's the rather classic CrossTab approach (see the link in my signature for details).

    ;WITH cte AS

    (

    SELECT DOC, DDN, TEL, ROW_NUMBER() OVER (PARTITION BY DOC ORDER BY DDN) AS rowNum

    FROM #CLI_NU

    )

    SELECT

    DOC,

    MAX(CASE WHEN rowNum = 1 THEN DDN ELSE NULL END) AS DDN1,

    MAX(CASE WHEN rowNum = 1 THEN TEL ELSE NULL END) AS TEL1,

    MAX(CASE WHEN rowNum = 2 THEN DDN ELSE NULL END) AS DDN2,

    MAX(CASE WHEN rowNum = 2 THEN TEL ELSE NULL END) AS TEL2,

    MAX(CASE WHEN rowNum = 3 THEN DDN ELSE NULL END) AS DDN3,

    MAX(CASE WHEN rowNum = 3 THEN TEL ELSE NULL END) AS TEL3

    FROM cte

    GROUP BY DOC



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • kramaswamy

    SSCoach

    Points: 18135

    Hah wow. Yeah I was thinking about using that approach but for some reason decided it wouldn't work - of course it does. Thanks for correcting it 😛

  • Lisset

    SSCommitted

    Points: 1858

    Thanks a lot to both! You are great!

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

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