Column to Rows.. - SQL Server Forums

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)

Column to Rows..

Author

Topic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-09-14 : 09:33:03

Hi All,

I have the records in the following format in a table.

JobTitle AB1 AB2 AB3 AB4 AB5 AB6 AB7 AB8 AB9 AB10 AB11 AB12
A001 1 Null Null Null Null Null Null Null Null 1 1 1
AOO2 1 Null Null Null Null Null Null Null Null 1 1 Null
A003 1 Null Null Null Null Null Null 1 Null 1 Null 1

Now i want the records in the following format
JobTitle CourseCode
A001 AB1
A001 AB10
A001 AB11
A001 AB12
A002 AB1
A002 AB10
A002 AB11
A003 AB1
A003 AB8
A004 AB10

Thanks for Anything kind of help on this..

Complicated things can be done by simple thinking

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-14 : 10:54:52

Here is one way:


set nocount on
declare @tb table (jobTitle varchar(4), AB1 int, AB2 int, AB3 int,
AB4 int, AB5 int, AB6 int,AB7 int, AB8 int, AB9 int,AB10 int, 
AB11 int, AB12 int)
insert @tb
select 'A001', 1, Null, Null, Null, Null, 
Null, Null, Null, Null, 1, 1, 1 union all
select 'A002', 1, Null, Null, Null, Null, 
Null, Null, Null, Null, 1, 1, Null union all
select 'A003', 1, Null, Null, Null, Null, 
Null, Null, 1, Null, 1, Null, 1


select	jobTitle
	,courseTitle
from	(
	select	jobTitle
		,courseTitle = 
		case
		when n = 1 and AB1 is not null then 'AB1'
		when n = 2 and AB2 is not null then 'AB2'
		when n = 3 and AB3 is not null then 'AB3'
		when n = 4 and AB4 is not null then 'AB4'
		when n = 5 and AB5 is not null then 'AB5'
		when n = 6 and AB6 is not null then 'AB6'
		when n = 7 and AB7 is not null then 'AB7'
		when n = 8 and AB8 is not null then 'AB8'
		when n = 9 and AB9 is not null then 'AB9'
		when n = 10 and AB10 is not null then 'AB10'
		when n = 11 and AB11 is not null then 'AB11'
		when n = 12 and AB12 is not null then 'AB12'
		else null
		end
		,n
	from	@tb
	cross join (select 1 n union select 2 union select 3 union
		select 4 union select 5 union select 6 union 
		select 7 union select 8 union select 9 union
		select 10 union select 11 union select 12) numbers
	) a
where	courseTitle is NOT NULL
order by jobTitle, n

Be One with the Optimizer
TG

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-09-14 : 12:29:07

Hi
Thanks for the reply but i guess these way will not help me much since.. There are around 240 columns in the table..

Actually i just exported the records from the excel. and now need to format in the specified format. and i got the stuck .. :-(

Complicated things can be done by simple thinking

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-14 : 12:53:48

>>There are around 240 columns in the table
I'm sure that seemed like a good idea at the time.

Any chance of a re-design?

btw, no reason you can't use the method (above) for your table. Just need to have has many numbers in the numbers table as there are columns in your table. And you can generate the CASE WHEN statments with something like this:


select 'when n = ' + convert(varchar, ordinal_position) 
	+ ' and ' + [column_name] + ' is not null then ''' 
	+ [column_name] + ''''
from	information_schema.columns
where	table_name = '<yourTAble>'
order by ordinal_position

Be One with the Optimizer
TG

magesh
Starting Member

23 Posts

Posted - 2005-09-15 : 05:06:40

Hi All,

some what i framed the query using dynamic SQL

CREATE TABLE [ROWCOLTBL] (
[JobTitle] [varchar] (50) COLLATE Japanese_CI_AS NULL ,
[COLA1] [int] NULL ,
[COLA2] [int] NULL ,
[COLA3] [int] NULL ,
[COLA4] [int] NULL ,
[COLA5] [int] NULL ,
[COLA6] [int] NULL ,
[COLA7] [int] NULL ,
[COLA8] [int] NULL ,
[COLA9] [int] NULL ,
[COLA10] [int] NULL
) ON [PRIMARY]
GO

SET NOCOUNT ON
GO
BEGIN TRANSACTION
INSERT INTO [ROWCOLTBL] VALUES ('A001','1',NULL,'1','1',NULL,'1','1','1','1',NULL);
INSERT INTO [ROWCOLTBL] VALUES ('A002',NULL,'1',NULL,'1',NULL,'1',NULL,'1',NULL,'1');
INSERT INTO [ROWCOLTBL] VALUES ('A003','1','1','1','1','1','1',NULL,'1',NULL,'1');
INSERT INTO [ROWCOLTBL] VALUES ('A004',NULL,'1','1','1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO [ROWCOLTBL] VALUES ('A005','1','1',NULL,NULL,'1','1',NULL,'1','1','1');
COMMIT
GO
SET NOCOUNT OFF
GO

----- Here is the query

DECLARE @SQL VARCHAR(2000), @TABLE VARCHAR(50)
SELECT @SQL = '', @TABLE = 'ROWCOLTBL'
SELECT @SQL = @SQL + 'SELECT JobTitle, '''
+ COLUMN_NAME + ''' AS COLUMNS,'
+ COLUMN_NAME + ' AS VALUE FROM '
+ TABLE_NAME + ' WHERE '
+ COLUMN_NAME + ' IS NOT NULL UNION '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@TABLE AND COLUMN_NAME<>'JobTitle'
SELECT @SQL = LEFT(@SQL,LEN(@SQL)-5)
PRINT @SQL
EXEC (@SQL)

-------

please modify the query according to your needs

Magesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources