CODEWITHSUNDEEP
sqlsql-server-2008
user1444165
user1444165

Reputation:

GROUP BY in combination with XML

I have a table similar to this (of course, this is just example content):

ID           xml          some_other_value      even_more_values           ....
-------------------------------------------------------------------------------
GUID1        <A />                      1   
GUID1        <A />                                            2
GUID1        <A />                      1                   
GUID2        <B />                      3                     4

Is it possible to group by ID, when one of the fields that should be grouped contains xml content? It is possible that duplicates exist in these rows, or column values can be null, but of course they never contain different values for the same ID.

I want the table to look like this: 

ID           xml          some_other_value      even_more_values            ....
--------------------------------------------------------------------------------
GUID1        <A />                      1                     2
GUID2        <B />                      3                     4

Any ideas?

Upvotes: 0

Views: 109

Answers (4)

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

maybe something like:

select * 
from
    (select 
      id
      ,max(isnull(someothervalue,0)) as someothervalue
      ....
    from table
    group by id) si
cross apply (select top 1 xml from table where id = si.id) xmli(xml)

Upvotes: 0

Pradeeshnarayan
Pradeeshnarayan

Reputation: 1235

you can try like this. Convert xml to varchar

select distinct t.id,t.xml,t1.first t1,t2.second t2
from test t
inner join test t1 on t1.id=t.id and t1.first is not null
inner join test t2 on t2.id=t.id and t2.second is not null;

FIDDLE

Upvotes: 0

podiluska
podiluska

Reputation: 51494

You can convert the xml to varchar and group by that

 group by convert(varchar(max),xml)

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You can use row_number() over().

select *
from (
       select *,
              row_number() over(partition by ID order by (select 0)) as rn
       from YourTable 

     ) T
where T.rn = 1

Upvotes: 2

Related Questions