CODEWITHSUNDEEP
sqlsql-server
ferensilver
ferensilver

Reputation: 351

How to get multiple rows of data into different columns

I have a table that stores an account id along with a value. An account can have multiple rows with one value per row. I am trying to create query that will give me one row per account id, with a column for each value (with a limit of 3 values)

Data Table Sample:

AccountID                               Value
8CD291F7-1A7E-409B-8D4C-7BECEE5DEBFB    34CD2F7D-E146-457E-B524-298D15EE0063
8CD291F7-1A7E-409B-8D4C-7BECEE5DEBFB    4FC58366-C797-4CAB-8A90-FE7425C1EDCD
8CD291F7-1A7E-409B-8D4C-7BECEE5DEBFB    10C64CEE-E026-4197-8BC9-CF5B0EE8ADBC            
02423075-6C7D-4AEB-AE8C-059E43914D53    8327537F-B0CB-43A4-B9BC-31E0A6377F99            
02423075-6C7D-4AEB-AE8C-059E43914D53    2EE12215-AF0D-4636-9E37-7F49EDA22AFF            
BEE8C007-ECA3-4327-8248-A1A66925EE9E    8FC3D058-0AC8-4295-A34D-28755DB52F54

Here is the query that I come up with so far:

SELECT C1.AccountId, C1.Value Value1, C2.Value Value2, C3.Value Value3
FROM
TempTable C1
JOIN
TempTable C2 ON C2.AccountId = C1.AccountId AND C2.Value != C1.Value
JOIN
TempTable C3 ON C2.AccountId = C3.AccountId AND C3.AccountId = C1.AccountId AND C3.Value != C2.Value AND C3.Value != C1.Value

Here are the issues that I have run into thus far:

  1. When I run the query with one account I get 150 records. If I put a DISTINCT in the select statement, it returns me 6 records.
  2. When I run distinct it creates a row for each possible combination. I do not care order the value columns are in.

Output with DISTINCT (with only one account - there will be a row for each account):

AccountId                                      Value1                                      Value2                                      Value3
8CD291F7-1A7E-409B-8D4C-7BECEE5DEBFB           34CD2F7D-E146-457E-B524-298D15EE0063        4FC58366-C797-4CAB-8A90-FE7425C1EDCD        10C64CEE-E026-4197-8BC9-CF5B0EE8ADBC
8CD291F7-1A7E-409B-8D4C-7BECEE5DEBFB           34CD2F7D-E146-457E-B524-298D15EE0063        10C64CEE-E026-4197-8BC9-CF5B0EE8ADBC        4FC58366-C797-4CAB-8A90-FE7425C1EDCD
8CD291F7-1A7E-409B-8D4C-7BECEE5DEBFB           4FC58366-C797-4CAB-8A90-FE7425C1EDCD        34CD2F7D-E146-457E-B524-298D15EE0063        10C64CEE-E026-4197-8BC9-CF5B0EE8ADBC
8CD291F7-1A7E-409B-8D4C-7BECEE5DEBFB           4FC58366-C797-4CAB-8A90-FE7425C1EDCD        10C64CEE-E026-4197-8BC9-CF5B0EE8ADBC        34CD2F7D-E146-457E-B524-298D15EE0063
8CD291F7-1A7E-409B-8D4C-7BECEE5DEBFB           10C64CEE-E026-4197-8BC9-CF5B0EE8ADBC        34CD2F7D-E146-457E-B524-298D15EE0063        4FC58366-C797-4CAB-8A90-FE7425C1EDCD
8CD291F7-1A7E-409B-8D4C-7BECEE5DEBFB           10C64CEE-E026-4197-8BC9-CF5B0EE8ADBC        4FC58366-C797-4CAB-8A90-FE7425C1EDCD        34CD2F7D-E146-457E-B524-298D15EE0063

Desired Result (with only one account- there will be a row for each account):

AccountId                              Value1                                 Value2                                 Value3
8CD291F7-1A7E-409B-8D4C-7BECEE5DEBFB   34CD2F7D-E146-457E-B524-298D15EE0063   4FC58366-C797-4CAB-8A90-FE7425C1EDCD   10C64CEE-E026-4197-8BC9-CF5B0EE8ADBC

Upvotes: 0

Views: 862

Answers (3)

ZLK
ZLK

Reputation: 2884

There are plenty of resources for using PIVOT and conditional aggregation to achieve a pivot. I much prefer using conditional aggregation because it's cleaner and easier to read (IMHO).

The only thing that's somewhat different about your case is you're using GUIDs (UNIQUEIDENTIFIER), which must be cast to another data type (e.g. VARCHAR(50)) before using MAX().

With only three values for each AccountID, here is one way you could achieve what you want by using conditional aggregation:

DECLARE @myTable TABLE (AccountID UNIQUEIDENTIFIER, Value UNIQUEIDENTIFIER);

INSERT @myTable (AccountID, Value)
VALUES ('8CD291F7-1A7E-409B-8D4C-7BECEE5DEBFB','34CD2F7D-E146-457E-B524-298D15EE0063'),
       ('8CD291F7-1A7E-409B-8D4C-7BECEE5DEBFB','4FC58366-C797-4CAB-8A90-FE7425C1EDCD'),
       ('8CD291F7-1A7E-409B-8D4C-7BECEE5DEBFB','10C64CEE-E026-4197-8BC9-CF5B0EE8ADBC'),    
       ('02423075-6C7D-4AEB-AE8C-059E43914D53','8327537F-B0CB-43A4-B9BC-31E0A6377F99'),
       ('02423075-6C7D-4AEB-AE8C-059E43914D53','2EE12215-AF0D-4636-9E37-7F49EDA22AFF'),
       ('BEE8C007-ECA3-4327-8248-A1A66925EE9E','8FC3D058-0AC8-4295-A34D-28755DB52F54');

SELECT AccountID,
       Value1 = MAX(CASE WHEN RN = 1 THEN Value END),
       Value2 = MAX(CASE WHEN RN = 2 THEN Value END),
       Value3 = MAX(CASE WHEN RN = 3 THEN Value END)
FROM
(
    SELECT AccountID,
           Value = CONVERT(VARCHAR(50), Value), -- Cast GUID as VARCHAR(50) to use MAX() in the outer query.
           RN = ROW_NUMBER() OVER (PARTITION BY AccountID ORDER BY Value)
    FROM @myTable
) T
GROUP BY AccountID;

Upvotes: 1

Halt_And_Catch_Fire
Halt_And_Catch_Fire

Reputation: 1

Not Sure if this what you are after

WITH cte as
(
SELECT '8CD291F7-1A7E-409B-8D4C-7BECEE5DEBFB' ID , '34CD2F7D-E146-457E-B524-298D15EE0063' Value UNION ALL
SELECT '8CD291F7-1A7E-409B-8D4C-7BECEE5DEBFB' ID , '4FC58366-C797-4CAB-8A90-FE7425C1EDCD' Value UNION ALL
SELECT '8CD291F7-1A7E-409B-8D4C-7BECEE5DEBFB' ID , '10C64CEE-E026-4197-8BC9-CF5B0EE8ADBC' Value UNION ALL
SELECT '02423075-6C7D-4AEB-AE8C-059E43914D53' ID , '8327537F-B0CB-43A4-B9BC-31E0A6377F99' Value UNION ALL
SELECT '02423075-6C7D-4AEB-AE8C-059E43914D53' ID , '2EE12215-AF0D-4636-9E37-7F49EDA22AFF' Value UNION ALL
SELECT 'BEE8C007-ECA3-4327-8248-A1A66925EE9E' ID , '8FC3D058-0AC8-4295-A34D-28755DB52F54' Value )
select c1.ID,c1.Value Value_1 ,c2.value 
INTO #temp
from cte c1 inner join cte c2 on c1.ID=c2.ID


WITH BaseQuery AS (
select ID,Value,Value_1 from #temp
)
SELECT ID,
[10C64CEE-E026-4197-8BC9-CF5B0EE8ADBC] V1,
[2EE12215-AF0D-4636-9E37-7F49EDA22AFF] V2,
[34CD2F7D-E146-457E-B524-298D15EE0063] V3,
[4FC58366-C797-4CAB-8A90-FE7425C1EDCD] V4,
[8327537F-B0CB-43A4-B9BC-31E0A6377F99] V5,
[8FC3D058-0AC8-4295-A34D-28755DB52F54] V6
FROM BaseQuery 
PIVOT(max(value_1) FOR Value in (
[10C64CEE-E026-4197-8BC9-CF5B0EE8ADBC],
[2EE12215-AF0D-4636-9E37-7F49EDA22AFF],
[34CD2F7D-E146-457E-B524-298D15EE0063],
[4FC58366-C797-4CAB-8A90-FE7425C1EDCD],
[8327537F-B0CB-43A4-B9BC-31E0A6377F99],
[8FC3D058-0AC8-4295-A34D-28755DB52F54])) AS PVT

Upvotes: 0

roloram
roloram

Reputation: 29

If you want to try an alternative to pivot tables, edit your select to making sure that c1.value is the smallest, and c2.value> c1.value, and c3.value> c2.value Sorry, I do not have a ssms right now to write and test the code

Upvotes: 0

Related Questions