pivot report

Howdy,

don't know where to start; it sounds trivial but i need some tip/hint on this one. it might need some dynamic sql with ref cursors but I'm not sure about the simpliest way...
this is the case:

i got this
+++++++++++
|Parent| Child|
+++++++++++
| 0600 |0700|
| 0600 |0701|
| 0600 |0702|
+++++++++++

and i like to transpose this to a table/query, looking like this.
+++++++++++++++++++++++++
|Parent | Child1 | Child2 | Child3|
+++++++++++++++++++++++++
| 0600 | 0700 | 0701 | 0702|
+++++++++++++++++++++++++

knowing the fact that I don't know how many children each parent has.
any idea's? it will be highly appriciated...

thanks a lot.

Hi Jetana,

There is an excellent article about PIVOT by Peter Larsson.

He takes care of every thing and even performance too.

Follow the link.

Pivot table for Microsoft SQL Server

Have a look and use it.

Thanks

Naras.

|||

Jetana:

Here might be one way of doing what you want:

declare @.hierarchy table
( parent integer,
child integer
)

insert into @.hierarchy
select 600, 700 union all
select 600, 701 union all
select 600, 702 union all
select 601, 800 union all
select 601, 801

select distinct
parent,
replace (
( select convert(varchar(11), child) as [data()]
from @.hierarchy b
where a.parent = b.parent
for xml path('')
), ' ', ', ') as children
from @.hierarchy a

/*
parent children
-- -
600 700, 701, 702
601 800, 801
*/

|||

Alright,

I'll try to be more specific.

Kent, your solution is cool, but it's not quite what I need, because I don't know the number of childs of each parent, so I can't hardcode them.

Naras, thanks but I already found that article while googling and it's way too complicated for my case.

Here's a solution I found, but I still have a problem, let me explain:

I got my 2 tables CHILD and PARENT, where CHILD is related to PARENT with an external key. here's a view I made that contains the parent with his children (a simple LEFT JOIN):

+++++++++++++++++++++++++++++
|Parent_ID | Child_Name |
+++++++++++++++++++++++++++++
| 1 | Child1_1
| 1 | Child1_2
| 1 | Child1_3
| 2 | Child2_1
| 2 | Child2_2
| 3 | Child3_1
| 4 | Child4_1
| 4 | Child4_2
| 4 | Child4_3
| 4 | Child4_4
| 4 | Child4_5
+++++++++++++++++++++++++++++

That's the code I made:

Code Snippet

declare @.RowNum int

declare @.asdf varchar(max)

declare @.MaxRow int

-- here I get the maximum number of children

--(it will be the number of columns of the result)

set @.MaxRow = (SELECT MAX(P.noChilds) FROM ( SELECT COUNT(Parent_ID) as noChilds

FROM view_ParentChild GROUP BY Parent_ID) P)

-- here I start to build the query string

set @.asdf = 'SELECT

Parent_ID,'

set @.RowNum = 0

-- for the number of children, and for each parent, I select

-- the children's name, or I put NULL if there's no more children

WHILE @.RowNum < @.MaxRow

BEGIN

set @.RowNum = @.RowNum + 1

set @.asdf = @.asdf + 'MAX(CASE ChildNo WHEN '+CAST(@.RowNum AS varchar(10))+' THEN Child_Name ELSE NULL END) AS [Child'+CAST(@.RowNum AS varchar(10))+'],'

END

set @.asdf = LEFT(@.asdf, LEN(@.asdf)-1) --I remove the last comma

-- here I build a query that contains the parent with all their

-- children, and I assign a number to each childre (1, 2, 3...)

-- for each parent.

set @.asdf = @.asdf +

' FROM (

SELECT

Parent_ID,

Child_Name,

ROW_NUMBER() OVER (PARTITION BY Parent_ID ORDER BY Child_Name) ChildNo

FROM

view_ParentChild

GROUP BY

Parent_ID,

Child_Name) C1

GROUP BY Parent_ID;'

EXEC(@.asdf)

And here's the result:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
|Parent_ID | Child_1 | Child_2 | Child_3 | Child_4 | Child_5 |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

| 1 | Child1_1 | Child1_2 | Child1_3 | NULL | NULL

| 2 | Child2_1 | Child2_2 | NULL | NULL | NULL

| 3 | Child3_1 | NULL | NULL | NULL | NULL

| 4 | Child4_1 | Child4_2 | Child4_3 | Child4_4 | Child4_5

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

So now I got what I wanted, but the problem is: I cannot put this code in a view! it says "The Declare cursor SQL construct or statement is not supported."

Any Idea how to solve that?

Thanks!

|||"... Kent, your solution is cool, but it's not quite what I need, because I don't know the number of childs of each parent, so I can't hardcode them. ..." What do you mean hardcoded? This is not hardcoded; it should be able to handle an indefinite number of children; moreover, it doesn't use cursors and it doesn't use dynamic SQL.|||

You're right Kent, I made a mistake.

Sorry about that...

I still need the children's value to be in different columns though.

|||

jetana wrote:

I cannot put this code in a view!

Absolutly you can't put this logic in your view. Since the number of columns are dynamic you can't achive this directly.

Recommanded solution :

Have a Staging Table populate the data (recreate the data & structre) & reuse it on rest of the palces.

Disadvantage:

There might be some latency occur.

|||

Alright then,

I think this answers my question.

Thanks to everybody for your help!