How to Reverse the Crosstab query output.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to Reverse the Crosstab query output.

Author

Topic

pareshgoyal
Starting Member

2 Posts

Posted - 2003-12-07 : 11:35:14

Hi,

I have following data in Excel.

Code GPH SSD MVX ....... CS150 ( around 150 columns with no fix column names)
B103 0 1 0 ...... 1 ( If yes this has been put as 1 otherwise 0)
B336 1 0 1 ....... 0
....
....
and so on....for around 400 rows.

I'll port this data to Access / SQL server 2000.

Now I want to get those data as follows -

Code CSCode
B103 SSD
B103 CS150
B336 GPH
B336 MVX
2 rows for Code B103 & 2 Rows B336....if more CSCodes are set as Yes then....those many rows should appear in the output for each Code.

Can anyone suggest any query.

Regards,
Paresh....

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-07 : 17:34:52

[code]
--Imported data with added identity column
create table import ( id int identity(1,1), Code char(4), GPH tinyint, SSD tinyint, MVX tinyint, CS150 tinyint)

--Sample Data
insert into import select 'B103',0,1,0,1
union all select 'B336',1,0,1,0
union all select 'B337',1,1,1,1

--Get a list of all CSCodes
select identity(int,1,1) id, column_name
INTO #Cols
FROM information_schema.columns
WHERE table_name = 'import' and column_name not in ('Code','ID')

--Create container for cross tabbed info
create table ##crosstab (Code varchar(4), CSCode varchar(20) )

declare @CodeID int; set @CodeID = 1 --Codes ID in Import Table
declare @CSCodeID int; set @CSCodeID = 1 --CSCodes ID in #Cols Table
declare @CSCode varchar(20) --CSCode
declare @sql varchar(4000)

while @CodeID <= (select max(id) from import)
begin
while @CSCodeID <=(select max(id) from #Cols)
begin
select @CSCode = COLUMN_NAME from #cols where id = @CSCodeID
set @sql = 'insert into ##crosstab select Code, ''' + @CSCode + ''' from import where id = ' + CAST(@CodeID as varchar(10)) + ' and ' + @CSCode + '= 1'
exec(@sql)
set @CSCodeID = @CSCodeID + 1; set @sql = ''
end
set @CSCodeID = 1
set @CodeID = @CodeID + 1
end

--select * from #cols
--select * from import
select * from ##crosstab

drop table import
drop table #cols
drop table ##crosstab
[/code]

pareshgoyal
Starting Member

2 Posts

Posted - 2003-12-08 : 07:58:43

Thanx a lot, nice solution....
can we have the same output without having a while loop, by using only few select statements will do.

Regards,
Paresh.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources