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

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

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?

---

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

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

---

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/

---

quote:

---

Originally posted by daniel50096230

Hi,

the page no is no longer needed.

Thanks for your help.

The solution works well.

Thanks.

---

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."

---