Post reply

Values from row to columns as indicator

  • December 14, 2009 at 1:52 pm

    #213600

    [font="Verdana"]Please can someone help me? I would like to create output table as below from following information using T-SQL or SSIS. Please let me know if anyone need more information.[/font]

    Reference Table:

    create table #ReferenceTable

    (ID int, Value Int)

    Insert into #ReferenceTable

    (Id, Value)

    Select '1','10' Union all

    Select '2','12' Union all

    Select '3','15' Union all

    Select '4','12' Union all

    Select '5','15' Union all

    Select '6','10' Union all

    Select '7','11' Union all

    Select '8','11' Union all

    Select '9','14'

    Select * from #ReferenceTable

    Value in reference table described as below:

    10 = Territory1

    11 = Territory2

    12 = Territory3

    14 = Territory4

    15 = Territory5

    I need output table as below:

    create table #OutputTable

    (ID int, Territory1 varchar(1), Territory2 varchar(1), Territory3 varchar(1), Territory4 varchar(1), Territory5 varchar(1))

    Insert into #OutputTable

    (ID, Territory1, Territory2, Territory3, Territory4, Territory5)

    Select '1','Y','','','','' Union all

    Select '2','','','Y','','' Union all

    Select '3','','','','','Y' Union all

    Select '4','','','Y','','' Union all

    Select '5','','','','','Y' Union all

    Select '6','Y','','','','' Union all

    Select '7','','Y','','','' Union all

    Select '8','','Y','','','' Union all

    Select '9','','','','Y',''

    Select * from #OutputTable

  • December 14, 2009 at 3:29 pm

    #1092268

    You might want to check out this article on Cross Tabs and Pivots[/url].

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • December 14, 2009 at 8:18 pm

    #1092317

    I just can't resist helping someone who took the time to make it so easy for me to help. Well done on the original post.

    This will do it. You could add explicit CASTs to guarantee a datatye of CHAR(1).

    SELECT Id,

    MAX(CASE WHEN Value = 10 THEN 'Y' ELSE '' END) AS Territory1,

    MAX(CASE WHEN Value = 11 THEN 'Y' ELSE '' END) AS Territory2,

    MAX(CASE WHEN Value = 12 THEN 'Y' ELSE '' END) AS Territory3,

    MAX(CASE WHEN Value = 14 THEN 'Y' ELSE '' END) AS Territory4,

    MAX(CASE WHEN Value = 14 THEN 'Y' ELSE '' END) AS Territory5

    FROM #ReferenceTable

    GROUP BY Id

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 14, 2009 at 9:31 pm

    #1092335

    Thank you very much to both of you!

  • December 14, 2009 at 9:47 pm

    #1092343

    Thank you very much to both of you!

  • December 14, 2009 at 9:58 pm

    #1092347

    You bet. Thank you for the feedback and thank you for taking the time to setup the code to make giving you an answer so very easy.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 12, 2010 at 11:19 am

    #1102622

    Hi,

    Is it possible to create this dynamic? So that if any new territory add then it create new column automatically?

    For ex If 16 = Territory6 then without changing code I get that column added with appropriate "Y" value in output table.

    I read many post for dynamic pivot but with limited knowldge I was not able to do in my scenario.

    Let me know if I am not clear.

    Thanks

  • January 12, 2010 at 10:26 pm

    #1102877

    Yes... it's absolutely possible. I realize that you've tried a couple of posts on dynamic SQL and haven't been able to get it because you're "new". However, you wouldn't have any fun nor learn anything new if I just gave it to you... please see the following article and give it a whirl. I'll give you a hint... you won't need the Tally table... just the dynamic SQL. Read the section of the article titled "[font="Arial Black"]Converting the Cross Tab to Dynamic SQL[/font]"

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 13, 2010 at 7:37 am

    #1103050

    Thank you Jeff.

    Of course I was looking hint and not answer. I am sure this will help me and if I am stuck then will get back to you/forum.

    Actually I am not technical guy but like to spend some time with databases and so I keep some sql related work with me and trying to learn based on other work schedule.

    Thanks again,

  • January 13, 2010 at 7:37 pm

    #1103393

    Outstanding. Thanks for the feedback. C'mon back if you get stuck.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

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