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:

  • how do I make a composite key with SQL Server Management Studio?
  • Intermittent SQL Exception - network-related or instance-specific error
  • How does BCP queryout encode varbinary(max) and can I change the encoding?
  • How to pivot dynamically with date as column
  • XPath to fetch SQL XML value
  • Extracting First Name and Last Name
  • 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  
    

    4 Solutions collect form web for “How to get multiple rows of data into different columns”

    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;
    

    Please have a look at this link : Pivot Table
    http://sqlmag.com/t-sql/create-pivoted-tables-3-steps

    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

    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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.