Concatenating strings using iteration

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 2005 Forums

Transact-SQL (2005)

Concatenating strings using iteration

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-03-27 : 05:13:42

I have the following table:

1	1	69	77	3658666	1	 WHEN 69 THEN 77
2	2	70	78	3658666	1	 WHEN 70 THEN 78
3	3	71	79	3658666	1	 WHEN 71 THEN 79
4	4	72	80	3658666	1	 WHEN 72 THEN 80
5	5	73	81	3658666	1	 WHEN 73 THEN 81
6	6	74	82	3658666	1	 WHEN 74 THEN 82
7	7	75	83	3658666	1	 WHEN 75 THEN 83
8	8	76	84	3658666	1	 WHEN 76 THEN 84
12	4	72	88	7317332	2	 WHEN 72 THEN 88
11	3	71	87	7317332	2	 WHEN 71 THEN 87
10	2	70	86	7317332	2	 WHEN 70 THEN 86
9	1	69	85	7317332	2	 WHEN 69 THEN 85

I want the following results:

Iteration	CaseStatement
1			WHEN 69 THEN 77 WHEN 70 THEN 78 WHEN 71 THEN 79 WHEN 72 THEN 80 WHEN 73 THEN 81 WHEN 74 THEN 82 WHEN 75 THEN 83WHEN 76 THEN 84
2			WHEN 72 THEN 88 WHEN 71 THEN 87 WHEN 70 THEN 86 WHEN 69 THEN 85

Thanks

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-27 : 05:25:40

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.63"
N 56°04'39.26"

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-03-27 : 06:21:33

Thanks Peso

I was stuck on this all last night and got no-where (I couldn't sleep, so was in bed working on my laptop).

Anyway, thanks for your reply, but I managed to fashion a solution using a CTE:

DECLARE @MaxNumberOfStoreIterations INT, @StoreNoCounter INT
SELECT  @MaxNumberOfStoreIterations = MAX(ID), @StoreNoCounter = 1 FROM #Test;

WITH CTE_CaseStatement (Iteration, StoreID, CaseStatement)
AS
(
SELECT Iteration, StoreID, CAST('CASE '+CaseStatement AS VARCHAR(8000))
FROM #Test
WHERE StoreID = 1
UNION ALL
SELECT t1.Iteration, t2.StoreID+1, t2.CaseStatement+t1.CaseStatement
FROM (SELECT Iteration, StoreID, CaseStatement FROM #Test)t1 
INNER JOIN CTE_CaseStatement t2
ON t1.StoreID	 = t2.StoreID+1
AND t1.Iteration = t2.Iteration
AND t2.StoreID+1 <= @MaxNumberOfStoreIterations 
)
UPDATE #Test
SET CaseStatement = cte.CaseStatement+' END' FROM CTE_CaseStatement cte
INNER JOIN
(
SELECT Iteration, MAX(StoreID)AS StoreID FROM CTE_CaseStatement
GROUP BY Iteration
)t1 ON t1.StoreID = cte.StoreID AND t1.Iteration = cte.Iteration

RESULTS:

1	1	69	77	3658666	1	CASE  WHEN 69 THEN 85 WHEN 70 THEN 86 WHEN 71 THEN 87 WHEN 72 THEN 88 END
2	2	70	78	3658666	1	CASE  WHEN 69 THEN 77 WHEN 70 THEN 78 WHEN 71 THEN 79 WHEN 72 THEN 80 WHEN 73 THEN 81 WHEN 74 THEN 82 WHEN 75 THEN 83 WHEN 76 THEN 84 END
3	3	71	79	3658666	1	CASE  WHEN 69 THEN 85 WHEN 70 THEN 86 WHEN 71 THEN 87 WHEN 72 THEN 88 END
4	4	72	80	3658666	1	CASE  WHEN 69 THEN 77 WHEN 70 THEN 78 WHEN 71 THEN 79 WHEN 72 THEN 80 WHEN 73 THEN 81 WHEN 74 THEN 82 WHEN 75 THEN 83 WHEN 76 THEN 84 END
5	5	73	81	3658666	1	CASE  WHEN 69 THEN 85 WHEN 70 THEN 86 WHEN 71 THEN 87 WHEN 72 THEN 88 END
6	6	74	82	3658666	1	CASE  WHEN 69 THEN 77 WHEN 70 THEN 78 WHEN 71 THEN 79 WHEN 72 THEN 80 WHEN 73 THEN 81 WHEN 74 THEN 82 WHEN 75 THEN 83 WHEN 76 THEN 84 END
7	7	75	83	3658666	1	CASE  WHEN 69 THEN 85 WHEN 70 THEN 86 WHEN 71 THEN 87 WHEN 72 THEN 88 END
8	8	76	84	3658666	1	CASE  WHEN 69 THEN 77 WHEN 70 THEN 78 WHEN 71 THEN 79 WHEN 72 THEN 80 WHEN 73 THEN 81 WHEN 74 THEN 82 WHEN 75 THEN 83 WHEN 76 THEN 84 END
12	4	72	88	7317332	2	CASE  WHEN 69 THEN 85 WHEN 70 THEN 86 WHEN 71 THEN 87 WHEN 72 THEN 88 END
11	3	71	87	7317332	2	CASE  WHEN 69 THEN 77 WHEN 70 THEN 78 WHEN 71 THEN 79 WHEN 72 THEN 80 WHEN 73 THEN 81 WHEN 74 THEN 82 WHEN 75 THEN 83 WHEN 76 THEN 84 END
10	2	70	86	7317332	2	CASE  WHEN 69 THEN 85 WHEN 70 THEN 86 WHEN 71 THEN 87 WHEN 72 THEN 88 END
9	1	69	85	7317332	2	CASE  WHEN 69 THEN 77 WHEN 70 THEN 78 WHEN 71 THEN 79 WHEN 72 THEN 80 WHEN 73 THEN 81 WHEN 74 THEN 82 WHEN 75 THEN 83 WHEN 76 THEN 84 END

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources