June 2, 2008 at 9:11 am
Hello, i have a table with data like
ColumnA
--------
1
2
3
4
5
and the output should be
ColumnX
--------
1,2,3,4,5
All this through a single sql statement..... i am not sure how to do this ....any help would be greatly appreciated.
Thanks in advance
June 2, 2008 at 10:24 am
Search for cross tab queries.
There isn't an easy way to do this. You can do with with self joins, i.e.
select a.colA + ',' + b.colA from tableA a inner join tableA b on a.pk = b.pk
June 2, 2008 at 12:50 pm
If we use a inner join
we get something like this
ColumnX
--------
1,1
2,2
3,3
4,4
5,5
And i need something like
ColumnX
--------
1,2,3,4,5
Is there any way we could use "for xml" to get the above output. I read somewhere we can do this but i have no clue as to how the sql will be written.
June 2, 2008 at 1:05 pm
You don't need to go the route of XML... see the following...
http://www.sqlservercentral.com/articles/Test+Data/61572/
--Jeff Moden
Change is inevitable... Change for the better is not.
June 2, 2008 at 1:20 pm
You can use coalesce for this kind of thing, but it's not "a single SQL statement", since it takes a few lines of code.
What Steve was suggesting, with "inner join" is something like:
select t1.colA + ',' + t2.colA + ',' + t3.colA ... (and so on, however many columns you are doing)
from Table1 t1
inner join Table1 t2
on t1.ColA = t2.ColA-1
inner join Table1 t3
on t2.ColA = t3.ColA-1
... etc.
Of course, that will only work if you have a fixed number of rows to turn into a single column.
Another way to do it is with the Pivot command. You might have to Pivot in a CTE, then concatenate the columns in an outer query. That would look something like:
create table #T (
ID int identity primary key,
GroupID tinyint not null,
ColA int not null)
insert into #T (GroupID, ColA)
select 1, 1
union all select 1, 2
union all select 1, 3
union all select 1, 4
union all select 1, 5
union all select 2, 10
union all select 2, 20
union all select 2, 30
union all select 2, 40
union all select 2, 50
;with
CTE1 (GroupID, Row, Val) as
(select groupid,
row_number() over (partition by groupid order by ColA),
ColA
from #T),
CTE2 (GroupID, Col1, Col2, Col3, Col4, Col5) as
(SELECT GroupID AS GroupID,
cast([1] as varchar(10)), cast([2] as varchar(10)),
cast([3] as varchar(10)), cast([4] as varchar(10)),
cast([5] as varchar(10))
FROM CTE1
PIVOT
(
max(Val)
FOR Row IN ([1], [2], [3], [4], [5])
) AS PivotTable)
select GroupID,
Col1 + ',' + Col2 + ',' + Col3 + ',' + Col4 + ',' + Col5 as ColX
from cte2
Note that, because of the way Pivot works, this will only work with groups of 5 rows. If you have some that have less, you'll need coalesce/isnull functions in the final concatenation. If you have ones with more, it won't get them.
Of course, the most efficient means of doing this is generally the running totals code, but that's also more than "one SQL statement".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 2, 2008 at 1:29 pm
you can get that using pivot transformation in SSIS...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply