CODEWITHSUNDEEP
t-sql
user472875
user472875

Reputation: 3185

T-SQL GROUP BY over a dynamic list

I have a table with an XML type column. This column contains a dynamic list of attributes that may be different between records.

I am trying to GROUP BY COUNT over these attributes without having to go through the table separately for each attribute.

For example, one record could have attributes A, B and C and the other would have B, C, D then, when I do the GROUP BY COUNT I would get A = 1, B = 2, C = 2 and D = 1.

Is there any straightforward way to do this?

EDIT in reply to Andrew's answer:

Because my knowledge of this construct is superficial at best I had to fiddle with it to get it to do what I want. In my actual code I needed to group by the TimeRange, as well as only select some attributes depending on their name. I am pasting the actual query below: 

WITH attributes AS (
  SELECT 
  Timestamp,
  N.a.value('@name[1]', 'nvarchar(max)') AS AttributeName,
  N.a.value('(.)[1]', 'nvarchar(max)') AS AttributeValue  
  FROM MyTable
  CROSS APPLY AttributesXml.nodes('/Attributes/Attribute') AS N(a)
)
SELECT Datepart(dy, Timestamp), AttributeValue, COUNT(AttributeValue)
FROM attributes
WHERE AttributeName IN ('AttributeA', 'AttributeB')
GROUP BY Datepart(dy, Timestamp), AttributeValue

As a side-note: Is there any way to reduce this further?

Upvotes: 0

Views: 285

Answers (2)

Andrew
Andrew

Reputation: 4624

WITH attributes AS (
  SELECT a.value('(.)[1]', 'nvarchar(max)') AS attribute
  FROM YourTable
  CROSS APPLY YourXMLColumn.nodes('//path/to/attributes') AS N(a)
)
SELECT attribute, COUNT(attribute)
FROM attributes
GROUP BY attribute

CROSS APPLY is like being able to JOIN the xml as a table. The WITH is needed because you can't have xml methods in a group clause.

Upvotes: 1

Rick Gittins
Rick Gittins

Reputation: 1138

Here is a way to get the attribute data into a way that you can easily work with it and reduce the number of times you need to go through the main table.

--create test data
declare @tmp table (
    field1 varchar(20),
    field2 varchar(20),
    field3 varchar(20))

insert into @tmp (field1, field2, field3)
values ('A', 'B', 'C'),
    ('B', 'C', 'D')

--convert the individual fields from seperate columns to one column
declare @table table(
    field varchar(20))

insert into @table (field)
select field1 from @tmp
union all
select field2 from @tmp
union all
select field3 from @tmp

--run the group by and get the count
select field, count(*)
from @table
group by field

Upvotes: 0

Related Questions