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 || 6How can I write my query to separate them become 6 column in the output using || as separator?Output C1 C2 C3 C4 C5 C61 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 C6FROM Table tCROSS APPLY dbo.ParseValues(t.columname ,'||')fGROUP BY columnname[/code]ParseValues function can be found herehttp://visakhm.blogspot.in/2010/02/parsing-delimited-string.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 || 12Then 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 67 8 9 10 11 12Is that possible? |
|
|
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 C6FROM tbl t CROSS APPLY dbo.ParseValues(t.columnname ,'||')fGROUP BY columnname, (ID - 1) / 7[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
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 || 12Then 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 67 8 9 10 11 12Is that possible?
will page repeat after 6 values always?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
daniel50096230
Yak Posting Veteran
99 Posts |
Posted - 2013-01-09 : 04:01:42
|
Yes.. The page always repeat after 9 , not 6.Thanks. |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 || 18The 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 C9FROM AES_LWM.CUSTOMER_CARD.RT_BackCard_Detail tCROSS APPLY AES_LWM.CUSTOMER_CARD.GetParseValues(t.Value ,'||')fGROUP BY (ID - 1) / 10The 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 |
|
|
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 C9FROM AES_LWM.CUSTOMER_CARD.RT_BackCard_Detail tCROSS APPLY AES_LWM.CUSTOMER_CARD.GetParseValues(t.Value ,'||')fGROUP BY (ID - 1) / 9[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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] |
|
|
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. |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-09 : 20:43:55
|
quote: Originally posted by visakh16ParseValues function can be found herehttp://visakhm.blogspot.in/2010/02/parsing-delimited-string.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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." |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 visakh16ParseValues function can be found herehttp://visakhm.blogspot.in/2010/02/parsing-delimited-string.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://visakhm.blogspot.com/ |
|
|
|