SQL Statement to return clomns as rows

Question

SQL Statement to return clomns as rows

David Tooker

Right there with Babe

Points: 716
More actions
June 28, 2010 at 12:19 pm

#224250

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

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

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

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

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.

LutzM SSC Guru Points: 107049 More actions · Answer 2

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]

David Tooker Right there with Babe Points: 716 More actions · Answer 3

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

WayneS SSC Guru Points: 95405 More actions · Answer 4

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