How to get identical values for different customers

Question

I have a table which has different customers and each customer is having different insurance. say

Customer Id Bank Account
1           Test insurance 1
1           Test Insurance 2
1           Test Insurance 3
2           Test Insurance 4
2           Test insurance 5

I want to write a query to get below output

Customer Id   Insurance1            Insurance2              Insurance3 --- 
1             Test insurance 1      Test Insurance 2        Test Insurance 3
2             Test Insurance 4      Test Insurance 5

Can anyone help me with query please. Note: insurance for each customer is unique

Hannah Vernon · Answer 1 · 2017-08-23T16:51:16.713

First, a simple test-bed setup:

USE tempdb;

IF OBJECT_ID(N'tempdb..#vals', N'U') IS NOT NULL
DROP TABLE #vals
CREATE TABLE #vals
(
    CustomerID int NOT NULL
    , AccountName varchar(25) NOT NULL
);

INSERT INTO #vals (CustomerID, AccountName)
VALUES (1, 'Test Insurance 1')
    , (1, 'Test Insurance 2')
    , (1, 'Test Insurance 3')
    , (2, 'Test Insurance 4')
    , (3, 'Test Insurance 5')

Assuming you want separate columns for each account name, you could use a PIVOT transform, combined with CROSS APPLY to do that:

SELECT *
FROM (
SELECT *
FROM #vals v
    CROSS APPLY (VALUES (AccountName))dummy(val)
) src
PIVOT (
    MAX(val)
    FOR AccountName IN (
        [Test Insurance 1]
        , [Test Insurance 2]
        , [Test Insurance 3]
        , [Test Insurance 4]
        , [Test Insurance 5]
    )
) pvt;

The CROSS APPLY creates a third column that we can use in the aggregate part of the PIVOT statement.

The results:

╔════════════╦══════════════════╦══════════════════╦══════════════════╦══════════════════╦══════════════════╗
║ CustomerID ║ Test Insurance 1 ║ Test Insurance 2 ║ Test Insurance 3 ║ Test Insurance 4 ║ Test Insurance 5 ║
╠════════════╬══════════════════╬══════════════════╬══════════════════╬══════════════════╬══════════════════╣
║ 1          ║ Test Insurance 1 ║ Test Insurance 2 ║ Test Insurance 3 ║ NULL             ║ NULL             ║
║ 2          ║ NULL             ║ NULL             ║ NULL             ║ Test Insurance 4 ║ NULL             ║
║ 3          ║ NULL             ║ NULL             ║ NULL             ║ NULL             ║ Test Insurance 5 ║
╚════════════╩══════════════════╩══════════════════╩══════════════════╩══════════════════╩══════════════════╝

Unfortunately, the above solution creates more columns than your question asks for. You might be interested in a solution that limits the number of columns to some predetermined number, as in:

;WITH src AS (
    SELECT v.CustomerID
        , v.AccountName
        , rn = ROW_NUMBER() OVER (PARTITION BY v.CustomerID ORDER BY v.AccountName)
    FROM #vals v
)
, s2 AS (
    SELECT src.CustomerID
        , Acct1 = CASE WHEN src.rn = 1 THEN src.AccountName ELSE NULL END
        , Acct2 = CASE WHEN src.rn = 2 THEN src.AccountName ELSE NULL END
        , Acct3 = CASE WHEN src.rn = 3 THEN src.AccountName ELSE NULL END
    FROM src
)
SELECT s2.CustomerID
    , Acct1 = MAX(s2.Acct1)
    , Acct2 = MAX(s2.Acct2)
    , Acct3 = MAX(s2.Acct3)
FROM s2
GROUP BY s2.CustomerID

The solution above requires you to predetermine the maximum number of columns you need to support.

The results:

╔════════════╦══════════════════╦══════════════════╦══════════════════╗
║ CustomerID ║      Acct1       ║      Acct2       ║      Acct3       ║
╠════════════╬══════════════════╬══════════════════╬══════════════════╣
║          1 ║ Test Insurance 1 ║ Test Insurance 2 ║ Test Insurance 3 ║
║          2 ║ Test Insurance 4 ║ NULL             ║ NULL             ║
║          3 ║ Test Insurance 5 ║ NULL             ║ NULL             ║
╚════════════╩══════════════════╩══════════════════╩══════════════════╝

If you are looking for the accounts to be included in a single column, you could use a subquery to obtain the list of accounts for each customer, something like this:

SELECT v.CustomerID
    , STUFF((SELECT ', ' + v1.AccountName FROM #vals v1 WHERE v1.CustomerID = v.CustomerID FOR XML PATH('')), 1, 2, '')
FROM #vals v
GROUP BY v.CustomerID
ORDER BY v.CustomerID;

╔════════════╦══════════════════════════════════════════════════════╗
║ CustomerID ║ (No column name)                                     ║
╠════════════╬══════════════════════════════════════════════════════╣
║ 1          ║ Test Insurance 1, Test Insurance 2, Test Insurance 3 ║
║ 2          ║ Test Insurance 4                                     ║
║ 3          ║ Test Insurance 5                                     ║
╚════════════╩══════════════════════════════════════════════════════╝

If you are using SQL Server 2017, you can take advantage of the STRING_AGG functionality to accomplish the same thing in a more elegant manner:

SELECT v.CustomerID
    , STRING_AGG(v.AccountName, ', ')
FROM #vals v
GROUP BY v.CustomerID

+------------+------------------------------------------------------+
| CustomerID |                   (No column name)                   |
+------------+------------------------------------------------------+
|          1 | Test Insurance 1, Test Insurance 2, Test Insurance 3 |
|          2 | Test Insurance 4                                     |
|          3 | Test Insurance 5                                     |
+------------+------------------------------------------------------+

How to get identical values for different customers

1 Answers1