Sql-server – Combining data from multiple rows

In sql-server-2005 or later, the the UNPIVOT and PIVOT operators can be used:

Sample data:

DECLARE @Source AS TABLE
(
    ID      char(1) PRIMARY KEY,
    PD1     integer NOT NULL,
    PD2     integer NOT NULL,
    PD3     integer NOT NULL,
    PD4     integer NOT NULL,
    PD5     integer NOT NULL
);

INSERT @Source
    (ID, PD1, PD2, PD3, PD4, PD5)
VALUES
    ('A', 1, 2, 3, 4, 5),
    ('B', 6, 7, 8, 9, 0);

Query:

SELECT 
    Pvt.Price, 
    Pvt.A, 
    Pvt.B
FROM @Source AS s
UNPIVOT
(
    Val 
    FOR Price IN (PD1, PD2, PD3, PD4, PD5)
) AS Unpvt
PIVOT 
(
    MAX(Val) 
    FOR ID IN (A, B)
) AS Pvt;

Output:

╔═══════╦═══╦═══╗
║ Price ║ A ║ B ║
╠═══════╬═══╬═══╣
║ PD1   ║ 1 ║ 6 ║
║ PD2   ║ 2 ║ 7 ║
║ PD3   ║ 3 ║ 8 ║
║ PD4   ║ 4 ║ 9 ║
║ PD5   ║ 5 ║ 0 ║
╚═══════╩═══╩═══╝

Execution plan:

The query can also be written without PIVOT and UNPIVOT:

SELECT
    f1.Price,
    A = MAX(CASE WHEN s.ID = 'A' THEN f1.Val END),
    B = MAX(CASE WHEN s.ID = 'B' THEN f1.Val END)
FROM @Source AS s
CROSS APPLY
(
    SELECT 'PD1', s.PD1 UNION ALL
    SELECT 'PD2', s.PD2 UNION ALL
    SELECT 'PD3', s.PD3 UNION ALL
    SELECT 'PD4', s.PD4 UNION ALL
    SELECT 'PD5', s.PD5
) AS f1 (Price, Val)
GROUP BY
    f1.Price;

Try the SQLFiddle here

This can be done using the PIVOT function, but since it sounds like you want to change the query based on the schemaId, then you will want to use dynamic SQL.

If you had a known number of values or knew the columns for a specific schemaID, then you could hard-coded the query. A static query would be:

select loannumber,
  [First Name], 
  [Middle Name], 
  [Last Name]
from
(
  select 
    l.loannumber,
    sf.fieldname,
    lf.fieldvalue
  from loans l
  left join loanfields lf
    on l.id = lf.loanid
  left join schemafields sf
    on lf.schemafieldid = sf.id
    and l.schemaid = sf.schemaid
) src
pivot
(
  max(fieldvalue)
  for fieldname in ([First Name], [Middle Name], [Last Name])
)piv;

See SQL Fiddle with Demo.

If you had an unknown number or you want the columns to change based on a SchemaId that you are passing into a procedure, then you will use dynamic SQL to generate the SQL string:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @schemaId int = 1

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(FieldName) 
                    from SchemaFields 
                    where schemaid = @schemaid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT loannumber,' + @cols + ' 
            from 
            (
              select 
                l.loannumber,
                sf.fieldname,
                lf.fieldvalue
              from loans l
              left join loanfields lf
                on l.id = lf.loanid
              left join schemafields sf
                on lf.schemafieldid = sf.id
                and l.schemaid = sf.schemaid
              where sf.schemaid = '+cast(@schemaid as varchar(10))+'
            ) x
            pivot 
            (
                max(fieldvalue)
                for fieldname in (' + @cols + ')
            ) p '

execute(@query);

See SQL Fiddle with Demo. Both of these queries will generate the result:

| LOANNUMBER | FIRST NAME | LAST NAME | MIDDLE NAME |
-----------------------------------------------------
|     ABC123 |       John |       Doe |      (null) |
|     XYZ789 |    Charles |     Smith |         Lee |