Split one column into multiple columns in SQL Server 2005


 
You can use a Split function and a CROSS APPLY to split a column into multiple columns.
(The Split function I used here is from:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Here is the script:
 

;with mycte as (select * from t1 t

CROSS

APPLY dbo.Split( ‘,’, t.AutoCombo))

SELECT

ID,AutoCombo,col1,[1] as A1,[2] as A2,[3] as A3,[4] as A4 FROM(

select

ID,AutoCombo,col1,pn, s FROM mycte ) src

PIVOT

( MAX(s) FOR pn in ([1],[2],[3],[4])) pvt

 

 

 
Sample table:

CREATE

TABLE [dbo].[t1](

[id] [int]

IDENTITY(1,1) NOT NULL,

[autoCombo] [varchar]

(250) NULL,

[col1] [varchar]

(50) NULL

)

ON [PRIMARY]

GO

SET

ANSI_PADDING OFF

GO

SET

IDENTITY_INSERT [dbo].[t1] ON

INSERT

[dbo].[t1] ([id], [autoCombo], [col1]) VALUES (1, CONVERT(TEXT, N’1002,1003,9856′), CONVERT(TEXT, N’somevaleue’))

INSERT

[dbo].[t1] ([id], [autoCombo], [col1]) VALUES (2, CONVERT(TEXT, N’1001′), CONVERT(TEXT, N’somevaleue1001′))

INSERT

[dbo].[t1] ([id], [autoCombo], [col1]) VALUES (3, CONVERT(TEXT, N’1005′), CONVERT(TEXT, N’somevaleue1005′))

INSERT

[dbo].[t1] ([id], [autoCombo], [col1]) VALUES (4, CONVERT(TEXT, N’1006′), CONVERT(TEXT, N’somevaleue1006′))

INSERT

[dbo].[t1] ([id], [autoCombo], [col1]) VALUES (5, CONVERT(TEXT, N’1007,1009′), CONVERT(TEXT, N’somevaleue1007′))

SET

IDENTITY_INSERT [dbo].[t1] OFF

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s