June 28, 2010 at 12:19 pm
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
June 28, 2010 at 1:46 pm
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.
June 28, 2010 at 2:34 pm
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...
June 29, 2010 at 8:25 am
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
June 29, 2010 at 10:01 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply