July 29, 2008 at 5:40 am
HI all,
I have created some sample sample data and what I would like my result set to be 🙂
[Code]
/*--RESULTS SHOULD BE
5 a
4 a
3 z|x
2 a
1 a|b|c
--**************************************/
DECLARE @tbl TABLE
(SECTION INT,Total INT,Col1 VARCHAR(10))
INSERT INTO @tbl
SELECT 1,1,'a' UNION ALL
SELECT 1,1,'b' UNION ALL
SELECT 1,1,'c' UNION ALL
SELECT 1,2,'a' UNION ALL
SELECT 1,3,'z' UNION ALL
SELECT 1,3,'x' UNION ALL
SELECT 1,4,'a' UNION ALL
SELECT 1,5,'a'
;WITH myCTE ([RowNum],[SECTION],[Total],[col1])
AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY Section ORDER BY [col1] ) as [RowNum],
SECTION,
Total,
col1
FROM @tbl
UNION ALL
SELECT
ROW_NUMBER() OVER (PARTITION BY a.Section ORDER BY a.[col1] ) as [RowNum],
c.SECTION,
c.Total,
CAST(a.col1 + '|' + c.Col1 as VARCHAR(10))
FROM @tbl a
INNER JOIN myCTE c ON a.Total = c.Total and a.Col1 != c.col1
)
SELECT *
FROM myCTE
[/code]
I can do this by adding the row above to the row below, but I'm strugling with multiple rows which is why I though of the recursive CTE method, but I can't get that working either.
I have a feeling it's something like ROw <Row +1 or something like that on a join somewhere.
Thanks in advance for any help.
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 29, 2008 at 6:02 am
HI All,
OK I managed to find a solution.
If someone can find a better one please let me know thanks:
;WITH myCTE ([id],[SECTION],[Total],[col1])
AS
(
SELECT
[id],
SECTION,
Total,
CAST(col1 as VARCHAR(10))as col1
FROM @tbl a
UNION ALL
SELECT
a.[id],
a.SECTION,
a.Total,
CAST(a.col1 + b.Col1 as VARCHAR(10)) as col1
FROM @tbl a
INNER JOIN myCTE b ON A.id > B.id AND a.Total = b.Total
)
SELECT a.SECTION,a.Total,a.Col1
FROM myCTE a
INNER JOIN (SELECT MAX(LEN(Col1)) as [Cnt],Total FROM myCTE GROUP BY Total) b
ON len(a.Col1) = b.cnt AND a.Total = b.Total
ORDER BY Total DESC
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply