August 18, 2017 at 9:32 am
Hi,
sample data to play with,Declare @TestData table (Id int);
insert into @TestData
select 100 union all
select 101 union all
select 102 union all
select 103 union all
select 104 union all
select 105 union all
select 106 union all
select 107 union all
select 108 union all
select 120 union all
select 121 union all
select 109 union all
select 111 union all
select 200 union all
select 201 union all
select 202
Expected Result:
select 100,101,102,103,104, 105 union all
select 106,107,108,120,121, 109 union all
select 111,200,201,202
Basically, I wanted to split records into group of 6 and delimit it with ",". Any sample query please how to achieve this. I tried this with the below query but not sure how to make the comma delimited on below querySELECT ROW_NUMBER() OVER (ORDER BY id)%7 AS grp from @TestData;
August 18, 2017 at 9:43 am
Your expected output can't be created. When using a Union, you need to return the same number of columns in every resultset. In yours, the first 2 have 6 columns, however, the last only 4.
Would you be expecting NULLs for the last two columns in the 3rd row?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 18, 2017 at 9:55 am
WITH Partitioned AS (
SELECT
(ROW_NUMBER() OVER (ORDER BY Id) - 1) / 6 AS PNo
, Id
FROM @TestData
)
SELECT DISTINCT
STUFF((
SELECT ', ' + CAST(Id AS char(3))
FROM Partitioned p1
WHERE p1.PNo = p2.PNo
FOR XML PATH ('')
),1,2,'') AS Concatenated
from Partitioned p2
John
August 18, 2017 at 9:58 am
Hi Tom,
Let it be empty with comma for last row like this 111,200,201,202,,,
Is it possible to do? any sample query please
August 18, 2017 at 10:00 am
Thank you John and your solution perfectly worked for me. much appreciated.
August 18, 2017 at 12:56 pm
You're welcome. I see what Thom was saying now. My solution concatenates the values into a single column. Your expected results contain six columns. But if your happy with what I did anyway, that's good.
John
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply