Comma separated values, xml, horizontal

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

General SQL Server Forums

New to SQL Server Programming

Comma separated values, xml, horizontal

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-01-10 : 19:09:00

I like to get from @table the Y field value and "Value" inside the xml from the x field (comma separated value).

declare @table table (y int, x xml)
insert @table
select 1, '<KPICounts>
<KPICount DataType="integer" Value="20" />
<KPICount Value="5" />
<KPICount Value="10" />
</KPICounts>' UNION ALL
select 2, '<KPICounts>
<KPICount Value="1" />
<KPICount Value="2" />
<KPICount Value="3" />
</KPICounts>'

DECLARE @AdAttributes XML
declare @y int
SELECT @AdAttributes= x, @y = y FROM @table
SELECT @y as 'types', Attr.col.value('@Value', 'int') AS 'Value'
FROM @AdAttributes.nodes('//KPICounts/KPICount') Attr(col)

result:

types values
1 20, 5, 10
2 1, 2, 3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 02:11:40

select y,x.query('data(/KPICounts/KPICount/@Value)')
from @table t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-01-11 : 05:25:03

Thanks V,
I wanted to add comma in between the values. I did it this way. Is it effective?

select replace(cast(a.typess as varchar(30)), ' ', ', ') from (
select x.query('data(/KPICounts/KPICount/@Value)') as 'typess'
from @table t ) a

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 05:58:36

quote:
Originally posted by basicconfiguration

Thanks V,
I wanted to add comma in between the values. I did it this way. Is it effective?

select replace(cast(a.typess as varchar(30)), ' ', ', ') from (
select x.query('data(/KPICounts/KPICount/@Value)') as 'typess'
from @table t ) a

looks fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-01-12 : 00:04:10

I'm close again. I want to get fieldy and type. To get type the emaildate field has to be between startdate and enddate. Where there are more than 2 types I want to put them in the same field as comma separated (CSV done previously. Thanks V,)
result below:

declare @table table (y int, x xml, emaildate datetime)
insert @table
select 1, '<UpSells>
<UpSell Type="HighlightListing" StartDate="12/23/11 11:08:25 PM" EndDate="12/30/11 11:08:25 PM" />
<UpSell Type="HighlightListing" StartDate="12/30/11 11:08:25 PM" EndDate="01/06/12 11:08:25 PM" />
</UpSells>', '2012-01-01' union all
select 2, '<UpSells>
<UpSell Type="Ray" StartDate="10/23/11 11:08:25 PM" EndDate="12/30/11 11:08:25 PM" />
<UpSell Type="Listing" StartDate="10/30/11 11:08:25 PM" EndDate="01/06/12 11:08:25 PM" />
</UpSells>', '2011-11-01' union all
select 3, '<UpSells>
<UpSell Type="Basketball" StartDate="12/23/11 11:08:25 PM" EndDate="12/30/11 11:08:25 PM" />
<UpSell Type="Prior" StartDate="12/30/11 11:08:25 PM" EndDate="01/06/12 11:08:25 PM" />
</UpSells>', '2012-01-01'

select a.fieldy, a.typess from (
select fieldy,
emaildate,
pref.value('(@Type)[1]', 'varchar(40)') as 'typess',
pref.value('(@StartDate)[1]', 'DATETIME') as 'StartDate',
pref.value('(@EndDate)[1]', 'DATETIME') as 'EndDate'

from @table
CROSS APPLY xfield.nodes ('UpSells/UpSell') AS People(pref)
where emaildate between pref.value('(@StartDate)[1]', 'DATETIME') and pref.value('(@EndDate)[1]', 'DATETIME'))a

result:
y type
1 HighlightListing
2 Ray, Listing
3 Prior

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-12 : 00:16:11

[code]
declare @table table (y int, x xml, emaildate datetime)
insert @table
select 1, '<UpSells>
<UpSell Type="HighlightListing" StartDate="12/23/11 11:08:25 PM" EndDate="12/30/11 11:08:25 PM" />
<UpSell Type="HighlightListing" StartDate="12/30/11 11:08:25 PM" EndDate="01/06/12 11:08:25 PM" />
</UpSells>', '2012-01-01' union all
select 2, '<UpSells>
<UpSell Type="Ray" StartDate="10/23/11 11:08:25 PM" EndDate="12/30/11 11:08:25 PM" />
<UpSell Type="Listing" StartDate="10/30/11 11:08:25 PM" EndDate="01/06/12 11:08:25 PM" />
</UpSells>', '2011-11-01' union all
select 3, '<UpSells>
<UpSell Type="Basketball" StartDate="12/23/11 11:08:25 PM" EndDate="12/30/11 11:08:25 PM" />
<UpSell Type="Prior" StartDate="12/30/11 11:08:25 PM" EndDate="01/06/12 11:08:25 PM" />
</UpSells>', '2012-01-01'

;with cte
as
(
select y,
emaildate,
pref.value('(@Type)[1]', 'varchar(40)') as 'typess',
pref.value('(@StartDate)[1]', 'DATETIME') as 'StartDate',
pref.value('(@EndDate)[1]', 'DATETIME') as 'EndDate'
from @table
CROSS APPLY x.nodes ('UpSells/UpSell') AS People(pref)
where emaildate between pref.value('(@StartDate)[1]', 'DATETIME') and pref.value('(@EndDate)[1]', 'DATETIME')
)
select y,
STUFF((select ',' + typess from cte where y = c1.y for XML path('')),1,1,'') AS [type]
from (select distinct y from cte)c1

output
---------------------------------------------------
y type
1 HighlightListing
2 Ray,Listing
3 Prior

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-01-16 : 04:57:13

Thanks Visak!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 05:28:19

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources