CODEWITHSUNDEEP
sqlsql-serverpivot-table
TalkingScientist
TalkingScientist

Reputation: 93

Create 1 Record from Multiple Rows

I'm trying to combine multiple rows from a single table that have a common identifier. So in the sample below, the GroupIdentifier of 1111111111111 becomes 1 record with each of the records being included as sub Field_#. Some records may have 3 results that are getting combined into 1 but some may only have 1 or 2.

Thanks

Sample Data

GroupIdentifier UniqueIdentifier    Direction   UserID

1111111111111   123456789       1           98685
1111111111111   123456790       2           4469
1111111111111   123456856       1           98685
1111115555555   123458765       2           5435
2222225353535   123454321       1           6565
2222225353535   123458765       3           4444

Expected output:

GroupIdentifier UniqueID_1  UniqueDirection_1   UserID_1    UniqueID_2  UniqueDirection_2   UserID_2    UniqueID_3  UniqueDirection_3   UserID_3
1111111111111   123456789   1                   98685       123456790   2                   4469        123456856   1                   98685
1111115555555   123458765   2                   5435
2222225353535   123454321   1                   6565        123458765   3                   4444

Upvotes: 4

Views: 213

Answers (2)

Patrick Hofman
Patrick Hofman

Reputation: 156948

You could use something like this. The with does the ordering to 1, 2, 3, etc.

with x
as
( select GroupIdentifier
  ,      UniqueIdentifier
  ,      Direction
  ,      rank() over (partition by GroupIdentifier order by Direction) index
  from   TABLE_NAME
)
select one.GroupIdentifier
,      one.UniqueIdentifier UniqueID_1
,      one.Direction UniqueDirection_1
,      one.UserID UserID_1
,      two.UniqueIdentifier UniqueID_2
,      two.Direction UniqueDirection_2
,      two.UserID UserID_2
,      three.UniqueIdentifier UniqueID_3
,      three.Direction UniqueDirection_3
,      three.UserID UserID_3
from   x one
left
outer
join   x two
on     one.GroupIdentifier = two.GroupIdentifier
and    two.index = 2
left
outer
join   x three
on     one.GroupIdentifier = three.GroupIdentifier
and    three.index = 3
where  one.index = 1

Upvotes: 0

Anon
Anon

Reputation: 10908

This is a "multi-column pivot". The easiest method is to do it manually with GROUP BY and MIN(CASE..), but it's also the least flexible if your design changes.

WITH t AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY GroupIdentifier ORDER BY DateTime ) AS SortOrder FROM MyTable)
SELECT
  GroupIdentifier,
  MIN(CASE SortOrder WHEN 1 THEN UniqueIdentifier END) UniqueIdentifier_1,
  MIN(CASE SortOrder WHEN 1 THEN Direction        END) Direction_1,
  MIN(CASE SortOrder WHEN 1 THEN UserID           END) UserID_1,
  MIN(CASE SortOrder WHEN 2 THEN UniqueIdentifier END) UniqueIdentifier_2,
  MIN(CASE SortOrder WHEN 2 THEN Direction        END) Direction_2,
  MIN(CASE SortOrder WHEN 2 THEN UserID           END) UserID_2,
  MIN(CASE SortOrder WHEN 3 THEN UniqueIdentifier END) UniqueIdentifier_3,
  MIN(CASE SortOrder WHEN 3 THEN Direction        END) Direction_3,
  MIN(CASE SortOrder WHEN 3 THEN UserID           END) UserID_3
) FROM t
GROUP BY GroupIdentifier

Upvotes: 3

Related Questions