SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Column field separated
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

daniel50096230
Yak Posting Veteran

Malaysia
97 Posts

Posted - 01/09/2013 :  00:34:03  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/09/2013 :  00:57:41  Show Profile  Reply with Quote

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



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

Malaysia
97 Posts

Posted - 01/09/2013 :  01:47:14  Show Profile  Reply with Quote
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)

Singapore
17638 Posts

Posted - 01/09/2013 :  02:13:36  Show Profile  Reply with Quote

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



KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/09/2013 :  02:55:50  Show Profile  Reply with Quote
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

Malaysia
97 Posts

Posted - 01/09/2013 :  04:01:42  Show Profile  Reply with Quote
Yes.. The page always repeat after 9 , not 6.

Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/09/2013 :  04:24:53  Show Profile  Reply with Quote
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

Malaysia
97 Posts

Posted - 01/09/2013 :  04:44:26  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/09/2013 :  05:25:58  Show Profile  Reply with Quote

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


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

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17638 Posts

Posted - 01/09/2013 :  06:41:11  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

daniel50096230
Yak Posting Veteran

Malaysia
97 Posts

Posted - 01/09/2013 :  06:46:32  Show Profile  Reply with Quote
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

USA
649 Posts

Posted - 01/09/2013 :  20:43:55  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/09/2013 :  22:28:10  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/09/2013 :  22:28:30  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000