split 1 column into 3 columns:

Question

Post reply

split 1 column into 3 columns:

SQL_path

SSCrazy

Points: 2810
More actions
May 23, 2011 at 10:50 am

#251485

Hi Everyone,
How can I break Column C :
A - B - C - D
1 - 61 - 1:00 -5/10/2011 2:00
1 - 61 - 2:00- 5/10/2011 2:00
1 - 61 - 3:00- 5/10/2011 2:00
2 - 61 - 2:00- 5/10/2011 4:00
2 - 61 - 3:00- 5/10/2011 4:00
2 - 61 - 4:00- 5/10/2011 4:00
INTO this:
A - B - C1 -c2 -c3 - D
1 - 61 - 1:00 -2:00- 3:00 - 5/10/2011 2:00
2 - 61 - 2:00 -3:00- 4:00 - 5/10/2011 4:00
Thanks for your help.

Viewing 10 posts - 1 through 10 (of 10 total)

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

ColdCoffee

SSC-Dedicated

Points: 39972

May 23, 2011 at 10:54 am

#1328591

Will all A B combination have only 3 rows each ?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

For your sample data:

declare @testtable table

( A int, B int, C time , D datetime)

insert into @testtable values

(1 , 61 , '1:00' ,'5/10/2011 2:00')

,(1 , 61 , '2:00', '5/10/2011 2:00')

,(1 , 61 , '3:00', '5/10/2011 2:00')

,(2 , 61 , '2:00', '5/10/2011 4:00')

,(2 , 61 , '3:00', '5/10/2011 4:00')

,(2 , 61 , '4:00', '5/10/2011 4:00')

; WITH CTE AS

(

SELECT A , B , c , D ,

RN = ROW_NUMBER() OVER( PARTITION BY A , B , D ORDER BY C )

FROM @testtable

)

SELECT A , B ,

C1 = MAX( CASE WHEN RN = 1 THEN C ELSE '0:00' END )

,C2 = MAX( CASE WHEN RN = 2 THEN C ELSE '0:00' END )

,C3 = MAX( CASE WHEN RN = 3 THEN C ELSE '0:00' END )

,D

FROM CTE

GROUP BY A , B , D

SQL_path SSCrazy Points: 2810 More actions · Answer 3

SQL_path

SSCrazy

Points: 2810

May 23, 2011 at 11:08 am

#1328603

No, it can change.

thanks.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

SQL_Nw (5/23/2011)
No, it can change.
thanks.

So you will need, C1, c2, c3, c4, c5.... etc etc depending on the rows, right?

SQL_path SSCrazy Points: 2810 More actions · Answer 5

Sorry, i was not clear, it will always be C1 c2 c3.

Thanks

SQL_path SSCrazy Points: 2810 More actions · Answer 6

Thanks.

I used your query and it did break the column into 3 columns.

However the data is being replaced as 0:00 at most of the places.

While it should be

1- 61- 1:00- 2:00- 3:00 - 5/10/2011 2:00

what am I missing.

Thanks..

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 7

Change the datatype of the column C in the table variable to VARCHAR(5)...

SQL_path SSCrazy Points: 2810 More actions · Answer 8

My procedure worked 🙂

I spent almost 2 days trying to figure this out. I could not have done it without your advice.

thanks so much for your help.

WayneS SSC Guru Points: 95461 More actions · Answer 9

So that someone in the future that stumbles across this thread knows, can you post your procedure here so that it can be referenced?

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