Post reply

How to add columns dynamically,.,..,.,

  • November 1, 2011 at 12:01 pm

    #262185

    In my table I have my dta like, all the data is same except the phone no.s in repeted rows.

    the code is for dynamic no.of repeted rows not like I have only 2 repeted columns and the query works for 2 columns only,not like that .

    The thing is I don't know how many no.of repeted rows are coming form the source side,its just an example I pasted here. I know the quere if the no.of repeated rows are known,I don't know how to get the output if the no.of repeated rows are unknown??,.,.

  • November 1, 2011 at 12:44 pm

    #1401789

    Sound like dynamic cross tab. Take a look at the articles from Jeff.

    The first is all about cross tab and the second is how to make it dynamic.

    http://www.sqlservercentral.com/articles/T-SQL/63681/[/url]

    http://www.sqlservercentral.com/articles/Crosstab/65048/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • November 1, 2011 at 1:19 pm

    #1401802

    I don't need any average or sum, I can use it in the query if I want but not in the output,.,.,

  • November 1, 2011 at 1:43 pm

    #1401819

    ramanamreddy (11/1/2011)

    I don't need any average or sum, I can use it in the query if I want but not in the output,.,.,

    Even though it doesn't look like it, you really do want to use an aggregate here. When dealing with strings you need to use Max() or Min(). Telephone numbers are best treated as strings despite looking like numbers.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • November 1, 2011 at 1:48 pm

    #1401823

    but in my requirement, my code works dynamically that means in my real work I don't know how many records are going to repeat with different phone no.s, I don't have a chance to use case statement as I don't know the no.of repeated rows. The case number has to be generated dynamically by considering the repeated input columns,.,.,

  • November 1, 2011 at 2:01 pm

    #1401839

    ramanamreddy (11/1/2011)

    but in my requirement, my code works dynamically that means in my real work I don't know how many records are going to repeat with different phone no.s, I don't have a chance to use case statement as I don't know the no.of repeated rows. The case number has to be generated dynamically by considering the repeated input columns,.,.,

    If you look back at Sean's post, you'll see that the second link specifically deals with dynamically generating the necessary columns.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • November 1, 2011 at 2:09 pm

    #1401845

    search this site (and others) for "FOR XML PATH"....this may be a quick fix for you....else as previously suggested you need to research dynamic cross tabs / dynamic pivots

    you also need to understand what "STUFF" does

    here is an example set of code using XML PATH....hope it helps

    -- create some data

    with produce (id,fruit, varieties)

    as (

    SELECT 101,'Apple', '3' UNION ALL

    SELECT 101,'Banana', '2' UNION ALL

    SELECT 102,'Orange', '1' UNION ALL

    SELECT 103,'Melon' ,'2' UNION ALL

    SELECT 103,'Grape' ,'1' UNION ALL

    SELECT 104,'Apple' ,'1' UNION ALL

    SELECT 105,'Banana' ,'1' UNION ALL

    SELECT 105,'Kiwi' ,'1' UNION ALL

    SELECT 105,'Tangerine' ,'1' UNION ALL

    SELECT 106,'Mango' ,'3' UNION ALL

    SELECT 106,'Melon' ,'2'

    )

    --query as follows

    SELECT id,

    Stuff((SELECT ',' + fruit + ' (' + varieties + ') : '

    FROM produce p2

    WHERE p1.id = p2.id

    ORDER BY p2.fruit --- sort by Fruit name

    FOR XML PATH('')), 1, 1, ' ')

    FROM produce p1

    GROUP BY id

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • November 1, 2011 at 2:27 pm

    #1401855

    Can you tell me how to put the repeated data values in rows into different columns in a single row??

    like

    id fname lname num

    1 kim john 1234

    1 kim john 2345

    as

    id fname lname num1 num2....

    1 kim john 1234 2345

  • November 1, 2011 at 2:32 pm

    #1401862

    It just isn't a "here is the code for you" type of solution. You are going to have to read the second article I linked and understand how to apply the logic to your situation. This is not easy stuff, mostly because this is not what sql was designed to do. It is likely going to take a few hours to understand the concepts and then how to apply them.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • November 1, 2011 at 2:34 pm

    #1401863

    thanks for your advice...

  • November 1, 2011 at 3:08 pm

    #1401890

    Sean Lange (11/1/2011)

    It just isn't a "here is the code for you" type of solution. You are going to have to read the second article I linked and understand how to apply the logic to your situation. This is not easy stuff, mostly because this is not what sql was designed to do. It is likely going to take a few hours to understand the concepts and then how to apply them.

    I would go even further. It's not easy stuff, because it violates basic principles of database design. Specifically, it violates first normal form by having a variable number of columns.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • November 1, 2011 at 10:50 pm

    #1401970

    ramanamreddy (11/1/2011)

    I don't need any average or sum, I can use it in the query if I want but not in the output,.,.,

    Hint: Use MAX for this instead of SUM... it's a very fast way to pivot the data.

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

  • November 1, 2011 at 10:52 pm

    #1401971

    I'll also tell you this... read the first link in my signature line below and post your data THAT way. You're much more likely to get a coded answer if you do what that article suggests.

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

  • November 4, 2011 at 9:32 am

    #1403331

    blah...

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 14 posts - 1 through 13 (of 13 total)

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