HOW to split records of one column into multiple column

Question

HOW to split records of one column into multiple column

honey47

SSC-Addicted

Points: 459
More actions
July 1, 2010 at 3:39 am

#226286

Deat All,
would any one suggest how to split the records of one column into multiple column
For EXample,
i ve two column in a table as
DATECODE
JuneA
JuneB
JuneC
JulyA
July B
But i want out put as
DATE CODEDATECODE
JuneAJuly A
JuneBJuly B
JuneC
i write the query:
select date,Case when date='June' then Code End as column1 ,
case when date='july 2010' then Code end as column2
FROm table
but didnt get the desire result
Thanks in advance

Viewing 9 posts - 1 through 8 (of 8 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi

Please review this sample table / data script to ensure that the column names & types are correct and that the data is representative of your problem.

CREATE TABLE #Temp ([DATE] VARCHAR(12), CODE CHAR(1))

INSERT INTO #Temp ([DATE], CODE)

SELECT 'June', 'A' UNION ALL

SELECT 'June', 'B' UNION ALL

SELECT 'June', 'C' UNION ALL

SELECT 'July', 'A' UNION ALL

SELECT 'July', 'B'

SELECT * FROM #Temp

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mike01 SSChampion Points: 11395 More actions · Answer 2

you can use a CTE like below, but because the 'Date' field is a varchar and is only the month name, then the sorting gets screwed up. If the date field were a smalldatetime, then the sorting would work better.

declare @Temp TABLE ([DATE] VARCHAR(12), CODE CHAR(1))

INSERT INTO @Temp ([DATE], CODE)

SELECT 'June', 'A' UNION ALL

SELECT 'June', 'B' UNION ALL

SELECT 'June', 'C' UNION ALL

SELECT 'July', 'A' UNION ALL

SELECT 'July', 'B'

;with cte as

(select DATE, code,

ROW_NUMBER() OVER (Partition BY code

ORDER BY Code, date desc) rownum

from (select DATE, Code from @Temp) v)

select a.date, a.code, b.date, b.code

from cte a

left outer join cte b

on a.code = b.code

and a.rownum = b.rownum - 1

Here's how it would look with dates

declare @Temp TABLE ([DATE] smalldatetime, CODE CHAR(1))

INSERT INTO @Temp ([DATE], CODE)

SELECT '6/1/2010', 'A' UNION ALL

SELECT '6/1/2010', 'B' UNION ALL

SELECT '6/1/2010', 'C' UNION ALL

SELECT '7/1/2010', 'A' UNION ALL

SELECT '7/1/2010', 'B'

;with cte as

(select DATE, code,

ROW_NUMBER() OVER (Partition BY code

ORDER BY Code, date) rownum

from (select DATE, Code from @Temp) v)

select datename(mm,a.date) A_Date, a.code, datename(mm,b.date) B_Date, b.code

from cte a

left outer join cte b

on a.code = b.code

and a.rownum = b.rownum - 1

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 3

Have you experimented with PIVOT yet? As usually coded, it wouldn't directly output your results as expected, but I bet you could nest a few PIVOT subqueries together to come up with the desired result set.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 4

If you data contains the actual month name, maybe you could do this within the CTE, as posted by Mike01:

...

PARTITION BY Code ORDER BY Code, CHARINDEX(data, 'JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember') ...

Scott Pletcher, SQL Server MVP 2008-2010

honey47 SSC-Addicted Points: 459 More actions · Answer 5

Thanks for all replies

i m new to sql queries. would u plz explain what is

cte

for what purpose it is used for..

Thanks

honey47 SSC-Addicted Points: 459 More actions · Answer 6

Hi mike ,

I use dhte query u suggested and got the output

A_DATE CODE B_DATE CODE

JuneAJulyA

JulyANULLNULL

JuneBJulyB

JulyBNULLNULL

JuneCNULLNULL

Is it need the out put as

A_DATE CODE B_DATE CODE

JuneAJulyA

JuneBJulyB

JuneCNULLNULL

Any further Suggestion would be appreciable

Thanks

Mike01 SSChampion Points: 11395 More actions · Answer 7

just add a where clause to the end of it

declare @Temp TABLE ([DATE] VARCHAR(12), CODE CHAR(1))

INSERT INTO @Temp ([DATE], CODE)

SELECT 'June', 'A' UNION ALL

SELECT 'June', 'B' UNION ALL

SELECT 'June', 'C' UNION ALL

SELECT 'July', 'A' UNION ALL

SELECT 'July', 'B'

;with cte as

(select DATE, code,

ROW_NUMBER() OVER (Partition BY code

ORDER BY Code, date desc) rownum

from (select DATE, Code from @Temp) v)

select a.date, a.code, b.date, b.code

from cte a

left outer join cte b

on a.code = b.code

and a.rownum = b.rownum - 1

where a.date = 'June'

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 8

CTE stands for Common Table Expression. There's no easy way to explain it except as a pre-query to your main query which can replace 1) a temp table, 2) replace subqueries, and 3) enable you to recursively check your tables for data and build on data that is already in the CTE.

Look up "common table expression" in Books Online and it will give more details and code examples. Then Google the term and you'll get all sorts of blogs that will explain it better.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.