Query results as comma delimited string without using PIVOT?
I want to create a query from multiple records as one record, but I don't want to use Pivot, FYI, I have> 260 records, not possible if I write Manual PIVOT, are there any easy solutions?
here's a table Example:
ID Element_Name Value
1 Parmitha 100
2 Anggun 200
3 Chandra 300
4 BagusofTerror 400
5 Laras 500
6 Jessica 600
7 Aisyah 700
......
200 Sonya 20000
and I want the result to be like this:
paramitha , anggun, chandra , bagusofterror, Laras , Jessica , Aisyah, ..... , Sonya
100 , 200, 300, 400,500,600,700,....,20000
source to share
Regarding http://blog.sqlauthority.com/2009/12/21/sql-server-comma-separated-values-csv-from-table-column-part-2/ you can use the following SQL query
I cannot run this, so I am not 100% sure it will work
SELECT STUFF(
(SELECT ',' + s.ElementName
FROM tableName s
FOR XML PATH('')),1,1,'')
UNION ALL
SELECT STUFF(
(SELECT ',' + s.Value
FROM tableName s
FOR XML PATH('')),1,1,'')
source to share
We used this query for cross-tabulation. Not sure if he's still ...
SELECT SUM(CASE WHEN id=1 THEN value ELSE 0 END) AS parmitha,
SUM(CASE WHEN id=2 THEN value ELSE 0 END) AS anggun,
SUM(CASE WHEN id=3 THEN value ELSE 0 END) AS chandra,
...etc...
SUM(value) AS total
FROM My_Table
WHERE ...etc...
I don't know C #, but I'm sure you can get it to loop over the column in order to prepare the SUM ().
source to share
How about what I tried on the sample dataset you provided.
declare @intFlag int
Declare @AnsString nvarchar(2000)
SET @intFlag=1
SET @AnsString=''
While (@intFlag<=200)
Begin
Select @AnsString=@AnsString
+CONVERT(Varchar(20),(SELECT [Element_Name]
FROM TABLE where RowID=@intFlag))
+', '
SET @intFlag=@intFlag+1
if @intFlag=199
break;
END
Print @AnsString
--Select @AnsSTring
source to share
If you need this data in separate columns, you can use dynamic SQL for PIVOT
data in SQL Server 2005+. Your code will look like this:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(element_name)
from yourtable
group by element_name, id
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select '+@cols+'
from
(
select Element_name, Value
from yourtable
) p
pivot
(
max(value)
for Element_name in('+@cols+')
) piv'
execute(@query)
See SQL Fiddle with Demo . This gives the result:
| PARMITHA | ANGGUN | CHANDRA | BAGUSOFTERROR | LARAS | JESSICA | AISYAH | SONYA |
----------------------------------------------------------------------------------
| 100 | 200 | 300 | 400 | 500 | 600 | 700 | 20000 |
source to share