SQL Server - Primary records with dependent as columns

Asked By Dotnet Developer on 04-Aug-10 05:58 AM
I have a query to fetch primary with all dependents that gives me result as:

ID Primary Name      depId DepName              Dep DOB

10 Liselotte E. Mitnick 11     Daniel W. Mitnick   1986-06-30

10 Liselotte E. Mitnick 13     Ali E. Mitnick         1986-06-30

What I want is:

1. If for a given record, it has 2 dependents then it should come as
separate columns.

2. For example, the above result set should come as

ID Primary Name       Dep1Id   Dep1Name         Dep1DOB    Dep1Gender
Dep2Id  Dep2Name

10 Liselotte E. Mitnick  11         Daniel W. Mitnick 1986-06-30        M
13      Ali E. Mitnick
10 Liselotte E. Mitnick           1986-06-30 F

Also, look at the original recordset that has the primary as well all
dependent records as rows. What i want is that there should just be one
primary record and all dependent records should come as columns to that
primary record.

Erland Sommarskog replied to Dotnet Developer on 04-Aug-10 03:46 PM
Dotnet Developer (DotnetDeveloper@) writes:

And if there are 3? 4?

Take your original query, and add this column:

rowno = row_number() OVER(PARTITION BY ID ORDER BY depID)

Then use this:

WITH numbered AS (
-- your query goes here
SELECT ID, PrimaryName,
Dep1ID   = MIN(CASE WHEN rowno = 1 THEN depID END),
Dep1Name = MIN(CASE WHEN rowno = 1 THEN depName END),
Dep1DOB  = MIN(CASE WHEN rowno = 1 THEN depDOB END),
Dep1Gender = MIN(CASE WHEN rowno = 1 THEN depGend END),
Dep2ID   = MIN(CASE WHEN rowno = 2 THEN depID END),
Dep2Name = MIN(CASE WHEN rowno = 2 THEN depName END),
Dep2DOB  = MIN(CASE WHEN rowno = 2 THEN depDOB END),
Dep2Gender = MIN(CASE WHEN rowno = 2 THEN depGend END)
FROM   numbered
GROUP  BY ID, PrimaryName


o   The solution reqiures SQL 2005. (Please always say which version of
SQL Server you use.)
o   If there can more dependents, you can extend the query, but you
need to know the upper limit beforehand; a query always return
a fixed set of known columns.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx