SQL Statement to return clomns as rows

  • I have data that was imported from excel spreadsheet into SQL as shown below

    PartNum ServerA ServerB ServerC ServerD

    123 P P Null F

    456 Null F P Null

    789 P P F Null

    1A2 Null P Null Null

    2A3 P Null P Null

    3A4 Null P Null Null

    The first col is the part number, then the columns are related to the different servers. The P and F are stating they passed or failed using the part number with that server. I need to move the data into a table where it lists the Servers, then the part number, only if it has a P or F in the Column ignoring nulls. Below is the way the final result should be.

    Server P/N Pass/Fail

    ServerA 123 P

    ServerA 789 P

    ServerA 2A3 P

    ServerB 123 P

    ServerB 456 F

    ServerB 789 P

    ServerB 1A2 P

    ServerB 3A4 P

    etc.

    Thanks for any Help on this.

    david

  • k I am sure there is a better solution for this that would require less code and no temp tables but this works at anyrate.

    declare @t table

    (PartNum varchar(3), ServerA char(1),ServerB char(1),ServerC char(1),ServerD char(1))

    insert into @t (PartNum,ServerA,ServerB,ServerC,ServerD) values('123','P','P',null,'F')

    insert into @t (PartNum,ServerA,ServerB,ServerC,ServerD) values('456',null,'F','P',null)

    insert into @t (PartNum,ServerA,ServerB,ServerC,ServerD) values('789','P','P','F',null)

    insert into @t (PartNum,ServerA,ServerB,ServerC,ServerD) values('1A2',null,'P',null,null)

    insert into @t (PartNum,ServerA,ServerB,ServerC,ServerD) values('2A3','P',null,'P',null)

    insert into @t (PartNum,ServerA,ServerB,ServerC,ServerD) values('3A4',null,'P',null,null)

    declare @rslttable table

    ([Server] varchar(7), PN varchar(3), PassFail char(1))

    insert into @rslttable

    Select 'ServerA',partnum,ServerA from @t where ServerA is not null

    insert into @rslttable

    Select 'ServerB',partnum,ServerB from @t where ServerB is not null

    insert into @rslttable

    Select 'ServerC',partnum,ServerC from @t where ServerC is not null

    insert into @rslttable

    Select 'ServerD',partnum,ServerD from @t where ServerD is not null

    select * from @rslttable

    If nothing else someone can use the insert statements to create a better solution.

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

  • Using Dans ready to use sample data I'd go with the following solution:

    SELECT ServerName,PartNum, PF

    FROM

    (SELECT PartNum,ServerA,ServerB,ServerC,ServerD

    FROM @t) p

    UNPIVOT

    (PF FOR ServerName IN

    (ServerA,ServerB,ServerC,ServerD)

    )AS unpvt

    ORDER BY ServerName,PartNum

    Side note: I have no idea how to get the rows ordered to match your expected result as shown based on the given information. If there would be an ID column it would be possible though...



    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]

  • I guess I should have been more specific. I can add a Id column without a problem. I only put the first few servers down as sample data. The server list for the columns goes on to about 40 or more columns. So I need something more dynamic rather than hardcoding the column names. Again, thanks for any insight or help on this.

    David

  • David Tooker (6/29/2010)


    I guess I should have been more specific. I can add a Id column without a problem. I only put the first few servers down as sample data. The server list for the columns goes on to about 40 or more columns. So I need something more dynamic rather than hardcoding the column names. Again, thanks for any insight or help on this.

    David

    I suggest that you read the two articles in my signature dealing with cross-tabs/pivot tables. Part 2 deals with dynamic pivot tables.

    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

Viewing 5 posts - 1 through 4 (of 4 total)

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