Post reply

SQL Help in populating a table

  • December 21, 2008 at 8:57 pm

    #129340

    I have a table XXX with only 1 record and columns as

    col1 col2 col3 col4 col5 col6 col7 col8

    50 60 10 30 40 25 5 10

    I need to populate the horizontal table vertically in a sorted order. as

    ColumnName ColumnValue

    col2 60

    col1 50

    col5 40

    col4 30

    col6 25

    col3 10

    col8 10

    col7 5

    I am using sql 2005. Is therea way i could get the results in the above form using a sql. currently i am creating a temp table and doing inserts for every col as

    insert into table1 (Colname, Colvalue)

    select 'col2', col2 from XXX

    insert into table1

    select 'col1',col1 from XXX and so on

    then sorting it by colvalue. This is too tedious. any easier method that i can use. Any help on this will be greatly appreciated..

  • December 21, 2008 at 9:46 pm

    #915949

    Try this script:

    Declare @TableName SYSNAME

    Declare @sql as NVarchar(MAX)

    Set @TableName = N'your table name here'

    Select @sql=Coalesce(@sql+'UNION ALL ', 'INSERT Into '+@TableName+'(ColumnName, ColumnValue)

    ')

    + 'Select ''' +Column_Name+ ''', ['+ Column_Name+ '] From ['+ @TableName+ ']

    '

    From INFORMATION_SCHEMA.COLUMNS

    Where Table_Name = @TableName

    Order by Ordinal_Position

    Print @sql

    EXEC (@sql)

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 22, 2008 at 9:07 am

    #916213

    Great, that worked really work. I would also like to know how can i autoincrement a column if its not an identity column. Since everytime the table inserts fresh data i need to start from 1 than from max(id) +1 ( as in autoidentity column).

    TIA

  • December 22, 2008 at 9:18 am

    #916219

    Depending on what you're going to do with that column, you could do it in many ways.

    Here are a couple:

    Trigger on the table with criteria on when to restart the count.

    ROW_NUMBER with criteria in the procedures you need that number for.

    If you just run something like a monthly report with that field, I'd use the ROW_NUMBER solution rather than having to generate and store that number all the time.

    If you use that number constantly in other processes, the trigger is probably the better way to go. To my knowledge, there's no way to do this with a DEFAULT value or a calculated field... but I've been wrong before.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • December 22, 2008 at 10:55 am

    #916273

    Incrementing a value

    An alternative way is to exploit MS SQL Server's proprietary update method of SET @variable = columnname = formula.

    Give a SERIOUS look at this great article:

    Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5

    By Jeff Moden, 2008/01/31

    http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

  • December 22, 2008 at 1:03 pm

    #916346

    Thanks for the kudo, J. :blush:

    --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 22, 2008 at 1:46 pm

    #916379

    Jeff Moden (12/22/2008)

    Thanks for the kudo, J. :blush:

    better than a pork chop 😀

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • December 22, 2008 at 2:01 pm

    #916393

    Jeff,

    I do not believe I could have figured that one out in a hundred. Still OOHing and AAAHing and drooling over that article. Time to print a clean copy...

    Now, where is that article on the pitfalls of CLR? Eh?

    Regards

  • December 22, 2008 at 3:10 pm

    #916434

    J (12/22/2008)

    Now, where is that article on the pitfalls of CLR? Eh?

    Here's the problem with that... long before the move to the new forum on SSC, Matt Miller and I did a bunch of testing with all manner of CLR's and posted the results. Except for RegExReplace, good ol' T-SQL pretty much beat up on every CLR tested. The problem is, I can't find those test posts after the move.

    Why did I have someone else write the CLR's for those tests?

    1. Make it fair... no mouse guarding the cheese.

    2. I didn't want to be "contanimated" ... 😛

    3. I don't know how to write C or C#. I don't even have it installed on my machine.

    4. I didn't want to "contanimate" my computer. 😛

    In other words, someone else is going to have to write that article because I can't actually test CLR's on my box. 🙂

    --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 22, 2008 at 3:14 pm

    #916438

    Perry Whittle (12/22/2008)

    Jeff Moden (12/22/2008)

    Thanks for the kudo, J. :blush:

    better than a pork chop 😀

    Heh... ya know? And doesn't the word "kudo" sound cool? Sounds like the name of a good breakfeast cereal... "Sit right down and pour yourself a bowl of "Kudos" in the morning". Or, maybe even a good beer... "Long day? Treat yourself to a "Kudo"... everything will be alright.".

    They make my day!

    --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 23, 2008 at 5:06 am

    #916687

    I always wondered what these kudos are... but never really bothered to look for it. Just now I looked into Wikipedia and found out that it comes from Greek, and as that it is originally a singular (the form "kudo" is quite recently reverse-engineered in English, where the original form makes impression of plural). I know this all is a bit off-topic, but anyway the question was answered and holidays are close, so lets be tolerant 🙂

    SELECT 'Happy ' + h.holiday_name + ' !'

    FROM holidays h

    WHERE h.culture_id = @yourculture

    AND h.start_date BETWEEN '20081223' AND '20090101'

    ORDER BY h.date

  • December 23, 2008 at 7:21 am

    #916751

    Yes, kudos is like an "Attaboy", only more prestigious. 😀

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 23, 2008 at 7:30 am

    #916753

    Yeah, attaboy is also interesting... I wonder whether it has something in common with the tropical leafcutter ants (Atta sp.) ? :hehe:

  • December 23, 2008 at 7:56 am

    #916762

    Vladan (12/23/2008)

    SELECT 'Happy ' + h.holiday_name + ' !'

    FROM holidays h

    WHERE h.culture_id = @yourculture

    AND h.start_date BETWEEN '20081223' AND '20090101'

    ORDER BY h.date

    LOL, that's awesome. Geek Humor rocks. :hehe:

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • December 23, 2008 at 10:00 am

    #916886

    Wouldn't a pivot statement work as well?

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

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