Selecting multiple rows of date into one row, based on a unque value

Question

I would like to select multiple rows of data into a single row.

The data I am working with has two columns:

loan numbers
names

There is a different row for each name attached to the loan number.

Example:

loan number 1 has 3 different names, therefore the loan number has 3 rows, one for each name.

What I want to do is, take each different name and assign it a new column so I have one unique row per loan number with each different name attached to it in their own column, rather than a row for each name.

Is this something that can be done in the select state using partition?

Can you show some sample data ? Do you want them names to be shown in separate columns or in one column ?? — M.Ali, Feb 16 '14 at 00:40
Part of the issue here is, do all of the loan numbers have the same number of names associated? What if Loan #1 has 2 names, but Loan #2 has 10 names? You want 8 empty columns? Take a look at my answer to this question that pulls 2 sets of data and then suggests how to combine it in C#. It's a different scenario but of a similar nature. http://stackoverflow.com/a/21768320/546000 — Dmitriy Khaykin, Feb 16 '14 at 01:02
@DavidKhaykin is right about the number of values per loan. But if a single column would work for you, there is a way to concatenate strings together in a single column -- FOR XML PATH http://stackoverflow.com/questions/6754889/for-xml-path-and-string-concatenation — wruckie, Feb 16 '14 at 01:29
Multiple columns is ok, so a different column for each name that exisits. Yes, there can be a different count of names per loan, so if columns are blank that is fine. — nation161r, Feb 18 '14 at 17:30

score 0 · Answer 1 · answered Feb 16 '14 at 00:49

I dont think you're going to do this with pure SQL. I'd try to use a stored procedure, and something like create temporary table. I think you'd have to dynamically build up a string to get sqlserver to execute, to create the table. Then you can do updates against it, and eventually return the single row using select.

Are you going to attempt to return multiple loans, one in each row (albeit, each with their loan names in differing rows)? Because if loan one had three names and loan two had four names, that's not going to work out in the same table.

How about pack all the names into a pipe-delimited string into column two, and the loan number in column one? A stored proc may still be needed but it'd be a lot easier than creating varying numbers of columns. Then split on the pipe on the client side of the database to get the names out.

score 0 · Answer 2 · answered Feb 16 '14 at 01:40

You can do this with dynamic pivots, although your column names become the user names, that's not a big deal if you're handling the output in c#; you can just iterate over a field count. One catch would be if you have two users with identical names, they would only show once.

Here's a great SO post on dynamic pivots.

Using this method I came up with the sample code below. (Note the line SET @loanID = 1 - change between 1 and 2 to test outputs)

IF OBJECT_ID('TEMPDB..#LOANS') IS NOT NULL
    DROP TABLE #LOANS

CREATE TABLE #LOANS (LOANID INT, USERNAME NVARCHAR(100))

INSERT INTO #LOANS 
SELECT 1, 'Neal' UNION ALL
SELECT 1, 'Bob' UNION ALL
SELECT 2, 'Sarah' UNION ALL
SELECT 2, 'John' UNION ALL
SELECT 2, 'Elsa' UNION ALL
SELECT 2, 'Jennifer' UNION ALL
SELECT 2, 'Dave'


DECLARE @cols AS NVARCHAR(MAX), @y AS INT, @sql AS NVARCHAR(MAX), @loanID INT

-- set an id here...
SET @loanID = 1

SET @cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT DISTINCT USERNAME AS y
        FROM #LOANS
        WHERE LOANID=@loanID) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');

SET @sql = N'SELECT *
FROM (SELECT LOANID, USERNAME 
      FROM #LOANS 
      WHERE LOANID='+CAST(@loanID AS NVARCHAR)+') AS D
PIVOT(MIN(USERNAME) FOR USERNAME IN(' + @cols + N')) AS P;';

EXEC sp_executesql @sql;
GO

If you had a userID, your results could look like this instead:

IF OBJECT_ID('TEMPDB..#LOANS') IS NOT NULL
    DROP TABLE #LOANS

CREATE TABLE #LOANS (LOANID INT, USERNAME NVARCHAR(100), USERID INT)

INSERT INTO #LOANS 
SELECT 1, 'Neal', 1 UNION ALL
SELECT 1, 'Bob', 2 UNION ALL
SELECT 2, 'Sarah', 3 UNION ALL
SELECT 2, 'John', 4 UNION ALL
SELECT 2, 'Elsa', 5 UNION ALL
SELECT 2, 'Jennifer', 6 UNION ALL
SELECT 2, 'Dave', 7


DECLARE @cols AS NVARCHAR(MAX), @y AS INT, @sql AS NVARCHAR(MAX), @loanID INT

-- set an id here...
SET @loanID = 1

SET @cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT DISTINCT USERID AS y
        FROM #LOANS
        WHERE LOANID=@loanID) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');

SET @sql = N'SELECT *
FROM (SELECT LOANID, USERNAME, USERID
      FROM #LOANS 
      WHERE LOANID='+CAST(@loanID AS NVARCHAR)+') AS D
PIVOT(MIN(USERNAME) FOR USERID IN(' + @cols + N')) AS P;';

EXEC sp_executesql @sql;
GO

Selecting multiple rows of date into one row, based on a unque value

2 Answers2