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 2008 Forums
 Transact-SQL (2008)
 Column field separated

Author  Topic 

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2013-01-09 : 00:34:03
Hi all,

I have the following data in one column using || as separator.
1 || 2 || 3 || 4 || 5 || 6

How can I write my query to separate them become 6 column in the output using || as separator?

Output
C1 C2 C3 C4 C5 C6
1 2 3 4 5 6

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-09 : 00:57:41
[code]
SELECT columnname,
MAX(CASE WHEN ID=1 THEN Val END) AS C1,
MAX(CASE WHEN ID=2 THEN Val END) AS C2,
MAX(CASE WHEN ID=3 THEN Val END) AS C3,
MAX(CASE WHEN ID=4 THEN Val END) AS C4,
MAX(CASE WHEN ID=5 THEN Val END) AS C5,
MAX(CASE WHEN ID=6 THEN Val END) AS C6
FROM Table t
CROSS APPLY dbo.ParseValues(t.columname ,'||')f
GROUP BY columnname
[/code]


ParseValues function can be found here

http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2013-01-09 : 01:47:14
Hi,

The answer is work. But I has a question here, how if the column contains the following data :

(Page1) || 1 || 2 || 3 || 4 || 5 || 6 || (Page2) || 7 || 8 || 9 || 10 || 11 || 12

Then I would like to put page1 into one row and page2 into second row?

I means when it meet a string called "Page", then it will split to another row?


Output:
1 2 3 4 5 6
7 8 9 10 11 12

Is that possible?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-01-09 : 02:13:36
[code]
SELECT columnname,
MAX(CASE WHEN (ID-1)%7=1 THEN Val END) AS C1,
MAX(CASE WHEN (ID-1)%7=2 THEN Val END) AS C2,
MAX(CASE WHEN (ID-1)%7=3 THEN Val END) AS C3,
MAX(CASE WHEN (ID-1)%7=4 THEN Val END) AS C4,
MAX(CASE WHEN (ID-1)%7=5 THEN Val END) AS C5,
MAX(CASE WHEN (ID-1)%7=6 THEN Val END) AS C6
FROM tbl t
CROSS APPLY dbo.ParseValues(t.columnname ,'||')f
GROUP BY columnname, (ID - 1) / 7
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-09 : 02:55:50
quote:
Originally posted by daniel50096230

Hi,

The answer is work. But I has a question here, how if the column contains the following data :

(Page1) || 1 || 2 || 3 || 4 || 5 || 6 || (Page2) || 7 || 8 || 9 || 10 || 11 || 12

Then I would like to put page1 into one row and page2 into second row?

I means when it meet a string called "Page", then it will split to another row?


Output:
1 2 3 4 5 6
7 8 9 10 11 12

Is that possible?



will page repeat after 6 values always?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2013-01-09 : 04:01:42
Yes.. The page always repeat after 9 , not 6.

Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-09 : 04:24:53
then change logic accordingly in Tans suggestion, 10 instead of 7

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2013-01-09 : 04:44:26
Hi, I tried but it seems incorrect.

My data is as below:
1 || 2 || 3 || 4 || 5 || 6 || 7 || 8 || 9 || 10 || 11 || 12 || 13 || 14 || 15 || 16 || 17 || 18

The query was like this:

SELECT MAX(CASE WHEN (ID-1)%10=1 THEN Val END) AS C1,
MAX(CASE WHEN (ID-1)%10=2 THEN Val END) AS C2,
MAX(CASE WHEN (ID-1)%10=3 THEN Val END) AS C3,
MAX(CASE WHEN (ID-1)%10=4 THEN Val END) AS C4,
MAX(CASE WHEN (ID-1)%10=5 THEN Val END) AS C5,
MAX(CASE WHEN (ID-1)%10=6 THEN Val END) AS C6,
MAX(CASE WHEN (ID-1)%10=7 THEN Val END) AS C7,
MAX(CASE WHEN (ID-1)%10=8 THEN Val END) AS C8,
MAX(CASE WHEN (ID-1)%10=9 THEN Val END) AS C9
FROM AES_LWM.CUSTOMER_CARD.RT_BackCard_Detail t
CROSS APPLY AES_LWM.CUSTOMER_CARD.GetParseValues(t.Value ,'||')f
GROUP BY (ID - 1) / 10


The output is incorrect:
C1 C2 C3 C4 C5 C6 C7 C8 C9
2 3 4 5 6 7 8 9 10
12 13 14 15 16 17 18 NULL NULL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-09 : 05:25:58
[code]
SELECT MAX(CASE WHEN (ID-1)%9=0 THEN Val END) AS C1,
MAX(CASE WHEN (ID-1)%9=1 THEN Val END) AS C2,
MAX(CASE WHEN (ID-1)%9=2 THEN Val END) AS C3,
MAX(CASE WHEN (ID-1)%9=3 THEN Val END) AS C4,
MAX(CASE WHEN (ID-1)%9=4 THEN Val END) AS C5,
MAX(CASE WHEN (ID-1)%9=5 THEN Val END) AS C6,
MAX(CASE WHEN (ID-1)%9=6 THEN Val END) AS C7,
MAX(CASE WHEN (ID-1)%9=7 THEN Val END) AS C8,
MAX(CASE WHEN (ID-1)%9=8 THEN Val END) AS C9
FROM AES_LWM.CUSTOMER_CARD.RT_BackCard_Detail t
CROSS APPLY AES_LWM.CUSTOMER_CARD.GetParseValues(t.Value ,'||')f
GROUP BY (ID - 1) / 9
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-01-09 : 06:41:11
quote:
Originally posted by daniel50096230

Hi, I tried but it seems incorrect.

My data is as below:
1 || 2 || 3 || 4 || 5 || 6 || 7 || 8 || 9 || 10 || 11 || 12 || 13 || 14 || 15 || 16 || 17 || 18



What happen to the (pagexx) ? ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2013-01-09 : 06:46:32
Hi,

the page no is no longer needed.

Thanks for your help.

The solution works well.

Thanks.
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2013-01-09 : 20:43:55
quote:
Originally posted by visakh16
ParseValues function can be found here

http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



I'd like to suggest that although your good code certainly works, it's going to be comparatively slow compared to a Tally Table or cteTally solution.

--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".

First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-09 : 22:28:10
quote:
Originally posted by daniel50096230

Hi,

the page no is no longer needed.

Thanks for your help.

The solution works well.

Thanks.


welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-09 : 22:28:30
quote:
Originally posted by Jeff Moden

quote:
Originally posted by visakh16
ParseValues function can be found here

http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



I'd like to suggest that although your good code certainly works, it's going to be comparatively slow compared to a Tally Table or cteTally solution.

--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".

First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."


yes ..thats true

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -