Prev: Decode XML Data Type
Next: linked servers between different sql versions??
From: Dotnet Developer on 4 Aug 2010 05:58
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.
From: Erland Sommarskog on 4 Aug 2010 15:46
Dotnet Developer (DotnetDeveloper(a)discussions.microsoft.com) writes:
> 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.

And if there are 3? 4?

> 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.

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

Notes:

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(a)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

 | 
Pages: 1
Prev: Decode XML Data Type
Next: linked servers between different sql versions??