Post reply

Converting Rows into Columns

  • November 23, 2010 at 7:31 am

    #226921

    Hi

    I can see this has been attempted in a few different ways already but they don't seem to fit my requirements so here goes.

    I have a table called tblAnalysisData as follows

    andSampleID andControlID andValue

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

    152272 1 12.26

    152272 2 2.6

    152272 3 1.8

    152272 4 99.2

    I need to be able select the data and out put it as one row with multiple columns

    andsampleID andControldID_1 andControldID_2 andControldID_3 andControldID_4

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

    152272 12.26 2.6 1.8 99.2

    additional information:

    There are upto 26 Rows per ID and so potentially 24 columns

    The andvalue column is varchar as it can contain text

    Any help greatly appreciated

    Thanks

  • November 23, 2010 at 9:58 am

    #1254563

    This should get you going. Just duplicate it for the other 26 columns.

    Also, please check out the Cross-Tab/Pivot tables, Parts 1 and 2 links in my signature.

    -- See how this starts off with a table and data in it?

    -- If you had provided us the data in this format,

    -- it would have made things easier for all of the

    -- volunteers on this site to help you out.

    DECLARE @tblAnalysisData TABLE (andSampleID INT,

    andControlID INT,

    andValue varchar(10));

    INSERT INTO @tblAnalysisData

    SELECT 152272, 1, '12.26' UNION ALL

    SELECT 152272, 2, '2.6' UNION ALL

    SELECT 152272, 3, '1.8' UNION ALL

    SELECT 152272, 4, '99.2';

    SELECT t.andSampleID,

    andControlID_1 = MAX(CASE WHEN t.andControlID = 1 THEN andValue ELSE NULL END),

    andControlID_2 = MAX(CASE WHEN t.andControlID = 2 THEN andValue ELSE NULL END),

    andControlID_3 = MAX(CASE WHEN t.andControlID = 3 THEN andValue ELSE NULL END),

    andControlID_4 = MAX(CASE WHEN t.andControlID = 4 THEN andValue ELSE NULL END)

    FROM @tblAnalysisData t

    GROUP BY t.andSampleID;

    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

  • November 23, 2010 at 10:03 am

    #1254568

    For a fast explaination, a fast answer. You're looking for the PIVOT command. Quite handy.

    If you'd like more tested code, please see the first link in my signature for creating consumable code and data to be tested against.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • November 24, 2010 at 1:20 pm

    #1255303

    Thanks guys will give that a go

  • November 24, 2010 at 11:53 pm

    #1255450

    Craig Farrell (11/23/2010)

    For a fast explaination, a fast answer. You're looking for the PIVOT command. Quite handy.

    If you'd like more tested code, please see the first link in my signature for creating consumable code and data to be tested against.

    Not an admonishment but I avoid both PIVOT and UNPIVOT because they're significantly slower, more CPU instensive, and more read intensive than a good ol' fashioned CROSS TAB or the relatively new way of doing an "un-Cross Tab" using CROSS APPLY. The stats on some testing is available near the end of the following article:

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

    --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, 2011 at 7:59 am

    #1424807

    I need to do the same thin....Convert:

    Co CodeFile #SSNLast NameFirst NameDeduction CodeDescription Deduction Amt

    3JA2007906111-22-3333AddisonJohnCHCH Vol Life $1.365

    3JA2007906111-22-3333AddisonJohnSPSP Vol Life $1.625

    3JA2007906111-22-3333AddisonJohn19Vision $7.323

    3HF5507945444-55-6666SmithLuisCHCH Vol Life $1.365

    3HF5507945444-55-6666SmithLuis19Vision $2.546

    3HF5507945444-55-6666SmithLuis11BCBS Dental $6.002

    3HF5507945444-55-6666SmithLuis13Life Insur $9.003

    To:

    Co CodeFile #SSNLast NameFirst NameDeduction CodeDescription Deduction Amt Deduction CodeDescription Deduction Amt

    3JA2007906111-22-3333AddisonJohnCHCH Vol Life $1.365 SPSP Vol Life $1.625

    ...and so on.

    Is there an easy way? Any help you could provide would be greatly appreciated.

    Thank you,

    Angela

  • December 22, 2011 at 8:40 am

    #1424828

    angela.nira (12/22/2011)

    Is there an easy way? Any help you could provide would be greatly appreciated.

    We're all unpaid volunteers. We're more than willing to help as long as you've shown that you've attempted to solve the problem on your own. It's obvious that you haven't put in any effort, because everything you need is contained in the thread that you posted to or in links mentioned in this thread. If you had read the thread and links, you would have had a much more specific question about the methods mentioned there and also would have been able to provide your attempt at the query along with error messages.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • December 22, 2011 at 8:53 am

    #1424837

    Hi Drew,

    What is obvious is that the users on this message board are much more knowledgable with Excel and SQL than I am and perhaps I should have mentioned that.

    I have made several attempts to manipulate the data based on the responses and have had no luck. Therefore, I have reverted to doing it manually.

    I am sure that I speak for most when I say we appreciate your efforts in helping one another.

    Thank you for your response.

  • December 22, 2011 at 9:46 am

    #1424861

    angela.nira (12/22/2011)

    Hi Drew,

    What is obvious is that the users on this message board are much more knowledgable with Excel and SQL than I am and perhaps I should have mentioned that.

    I have made several attempts to manipulate the data based on the responses and have had no luck. Therefore, I have reverted to doing it manually.

    I am sure that I speak for most when I say we appreciate your efforts in helping one another.

    Thank you for your response.

    The main reason you did not receive any help is because it appears that you didn't put much effort into this. Being volunteers, it is much preferred to have ddl (create table statements), sample data (insert statements) and desired output based on your sample data. The first link in my signature is great source of information on how to best post questions to receive tested answers.

    Read up above in the post by Wayne where he says to look at the links in his signature about Cross tabs. It appears from your data that you are going to need to spend a lot of time reading part2 about dynamic cross tabs because you have varying row counts for each person. This type of thing is incredibly difficult to pull of in tsql because it just isn't designed for that type of data manipulation. This type of thing is typically handled much better in the front end (even Excel can handle this stuff better). Essentially you are asking sql to run a query that returns a dynamic number of columns based on the dynamic data it finds.

    If you post the ddl, sample data and desired output you will have a MUCH greater chance of getting some help. The important thing is to help us help you.

    _______________________________________________________________

    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/

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

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