Hi I have a table called "Member" as given below..

familyID memberID firstName
-------- -------- --------------------
0 7 Stuart
0 5 Kasey
0 1 Sally
0 2 Cooper
1 9 Rosemary
2 3 Lisa
2 6 Stephanie
3 4 mandy
3 8 Fisher


I want to create a view, storedProcedure or a Function (whatever is
possible in SQL Server 2000) that returns data that looks something
like this:


familyID member1 member2 member3 member4 (columns can go to..
memberN )
------- ---------- ---------- --------------
0 Stuart Kasey Sally Cooper
1 Rosemary
2 Lisa Stephanie
3 Mandy Fisher


Any help would be greatly appreciated..

[quoted text, click to view]

(excerpt from "Using DB2 UDB OLAP Functions"
http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0401kuznetsov/index.html
)


Laying out two records on a line using a sequence table

Given this table structure and data:


CREATE TABLE VEHICLE_ACCIDENT(
ACCIDENT_ID INT NOT NULL,
TAG_NUMBER CHAR(10) ,
TAG_STATE CHAR(2) );
INSERT INTO VEHICLE_ACCIDENT VALUES(1,'123456','IL'),(1,'234567','IL'),
(1,'34567TT','WI');



(other columns omitted to keep things simple). Note that there may be
more than 2 vehicles involved in an accident. There is a requirement
to lay out two records on one line, like this (when three vehicles
were involved):


TAG_NUMBER_1 TAG_STATE_1 TAG_NUMBER_2 TAG_STATE_2
------------ ----------- ------------ -----------
123456 IL 234567 IL
3456TT WI



Using ROW_NUMBER(), this can be done very easily:


WITH VEHICLE_ACCIDENT_RN(ACCIDENT_ID, ROWNUM, TAG_NUMBER, TAG_STATE)
AS
(SELECT ACCIDENT_ID, ROW_NUMBER() OVER() AS ROWNUM, TAG_NUMBER,
TAG_STATE
FROM VEHICLE_ACCIDENT)
SELECT
LEFT_SIDE.TAG_NUMBER AS TAG_NUMBER_1,
LEFT_SIDE.TAG_STATE AS TAG_STATE_1,
RIGHT_SIDE.TAG_NUMBER AS TAG_NUMBER_2,
RIGHT_SIDE.TAG_STATE AS TAG_STATE_2
FROM
(SELECT L.*, (L.ROWNUM+1)/2 AS PAGENUM FROM VEHICLE_ACCIDENT_RN L
WHERE MOD(ROWNUM,2)=1)AS LEFT_SIDE
LEFT OUTER JOIN
(SELECT R.*, (R.ROWNUM+1)/2 AS PAGENUM FROM VEHICLE_ACCIDENT_RN R
WHERE MOD(ROWNUM,2)=0)AS RIGHT_SIDE
ON LEFT_SIDE.PAGENUM = RIGHT_SIDE.PAGENUM
WHERE LEFT_SIDE.ACCIDENT_ID=1 AND (RIGHT_SIDE.ACCIDENT_ID=1 OR
RIGHT_SIDE.ACCIDENT_ID IS NULL)



This query gives correct results for both odd and even number of
involved vehicles. Feel free to add records and verify it. Again, as
in the previous chapters, there are several other ways to solve this
problem. Using ROW_NUMBER() allows for a solution that is very simple,
quick to develop and easy to understand.


-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

[quoted text, click to view]

A table has a fixed number of columns by definition. This is a
display problem and should be handled in the front end, not the
database. There are some kludges that will violate 1NF by cramming
strings into a single column, but good SQL programmers do not use
them.

If you have 2005, you can use
WITH (
SELECT family_id, first_name,
ROW_COUNT() OVER (PARTITION BY family_id ORDER BY member_id)
FROM Membership)
AS X (family_id, first_name, place)

SELECT family_id,
MAX(CASE WHEN place = 1 THEN first_name ELSE NULL END) AS M1,
MAX(CASE WHEN place = 2 THEN first_name ELSE NULL END) AS M2,
..
MAX(CASE WHEN place = n THEN first_name ELSE NULL END) AS Mn
FROM X
GROUP BY family_id;

If ther are more than (n) members, this fails. What you can do is
make the last column:

SUM(CASE WHEN place >= n THEN 1 ELSE 0 END) AS more_members

This at least will get you some information.

[quoted text, click to view]

A good idea!

It can work for any number of rows if you modify it as follows (I
borrowed Uri's data, thanks Uri):

drop table t
go
create table t (familyID int,memberID int ,firstName varchar(20))
go
insert into t values (0,7,'Stuart')
insert into t values (0,5,'Kasey')
insert into t values (0,1,'Sally')
insert into t values (0,2,'Cooper')
insert into t values (1,9,'Rosemary')
insert into t values (2,3,'Lisa')
insert into t values (2,6,'Stephanie')
insert into t values (3,4,'mandy')
insert into t values (3,8,'Fisher')
insert into t values (3,9,'Uri')
go

SELECT familyID,
MAX(CASE WHEN place % 3 = 0 THEN firstName ELSE NULL END) AS
M1,
MAX(CASE WHEN place % 3 = 1 THEN firstName ELSE NULL END) AS
M2,
MAX(CASE WHEN place % 3 = 2 THEN firstName ELSE NULL END) AS M3
FROM (
SELECT familyID, firstName,
ROW_NUMBER() OVER (PARTITION BY familyID ORDER BY firstName) -
1 place
FROM t
) q
GROUP BY familyID, place/3
ORDER BY familyID, place/3;

/*
familyID M1 M2 M3
----------- -------------------- --------------------
--------------------
0 Cooper Kasey Sally
0 Stuart NULL NULL
1 Rosemary NULL NULL
2 Lisa Stephanie NULL
3 Fisher mandy Uri
Warning: Null value is eliminated by an aggregate or other SET
operation.

(5 row(s) affected)
*/

BTW I recall you were scolding somebody for sorting on a column not
included in the result set. To follow your own ideas, I replaced
(PARTITION BY familyID ORDER BY memberID) with (PARTITION BY familyID
ORDER BY firstName), because memberID is not exposed in the result
set.
However, I think that your own principle of sorting on only columns
included in the result set makes so little sence that you yourself do
not follow it ;););).

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

HEre's what i came up with:

create proc GetFams

as
declare @Sql varchar(8000),
@MemberId int
set @Sql = ''

declare c cursor fast_forward for
select distinct memberID
from t

open c
fetch next from c into @MemberId

while @@Fetch_Status = 0
begin
set @Sql = @Sql + '
, max(case when MemberId = ' + cast(@MemberId as varchar) + ' then
firstname end) Member' + cast(@MemberId as varchar)
fetch next from c into @MemberId
end
close c
deallocate c

set @Sql = 'Select FamilyId ' + @Sql + '
[quoted text, click to view]
group by familyId
order by FamilyId'

print @Sql
exec (@Sql)


When executed on the table supplied by Uri, it generates this SQL for
you:
Select FamilyId
, max(case when MemberId = 1 then firstname end) Member1
, max(case when MemberId = 2 then firstname end) Member2
, max(case when MemberId = 3 then firstname end) Member3
, max(case when MemberId = 4 then firstname end) Member4
, max(case when MemberId = 5 then firstname end) Member5
, max(case when MemberId = 6 then firstname end) Member6
, max(case when MemberId = 7 then firstname end) Member7
, max(case when MemberId = 8 then firstname end) Member8
, max(case when MemberId = 9 then firstname end) Member9
[quoted text, click to view]
group by familyId
order by FamilyId

which crosstabs it all nicely. The only thing is that the MemberId is
not filled densely, so now it goes up to member9, while the biggest
family, 0, only has 4 members. To solve this, renumber the MemberId
column.

Also, the max length for @Sql is 8000 chars, so you'll run into
trouble with more than 130 members in a familiy. You'd be able to
stretch that some more by using shorter names for column.

Good luck,

John

[quoted text, click to view]

BTW, s see that the Row_Number function will solve the issue with the
MemberId. Has anyone gotten the Pivot syntax of sql2005 working yet? I
can't seem to get my head around it. Does it do dynamic crosstabs, or
only hardcoded, where you need to know the columns in advance?

Regards,

John

Rex
I think you have to do such operation on the client side

create table t (familyID int,memberID int ,firstName varchar(20))
insert into t values (0,7,'Stuart')
insert into t values (0,5,'Kasey')
insert into t values (0,1,'Sally')
insert into t values (0,2,'Cooper')
insert into t values (1,9,'Rosemary')
insert into t values (2,3,'Lisa')
insert into t values (2,6,'Stephanie')
insert into t values (3,4,'mandy')
insert into t values (3,8,'Fisher')





create function dbo.fn_GettingTree
(
@familyID AS int
)
returns @tree table
(
memberID int NOT NULL,
familyID int NULL,
firstName varchar(25) NOT NULL,
lvl int NOT NULL
)
as

begin

declare @lvl AS int, @path AS varchar(900)
select @lvl = 0, @path = '.'

insert into @tree
select memberID, familyID, firstName,@lvl
from t
where memberID = @familyID

while @@rowcount> 0
begin
set @lvl = @lvl + 1

insert into @tree
select E.memberID, E.familyID, E.firstName, @lvl
from t AS E join @tree AS T
onE.familyID = T.memberID and T.lvl = @lvl - 1
end

return

end


SELECT *
FROM fn_GettingTree(1)
GO


[quoted text, click to view]

John, your solution is closest to what I am want but there is only one
problem and that is with the memberID.. GetFams procedure creates
unique memberID as columns and I dont want that because lots of my
fileds will be Null, instead I have one more field in my table which
specifies the type of member and the filed is called 'memType'

so the new table would be:

create table t (familyID int, memberID int ,firstName varchar(20),
memType int)
insert into t values (0,7,'Stuart',2)
insert into t values (0,5,'Kasey',3)
insert into t values (0,1,'Sally',1)
insert into t values (0,2,'Cooper',4)
insert into t values (1,9,'Rosemary',1)
insert into t values (2,3,'Lisa',1)
insert into t values (2,6,'Stephanie',3)
insert into t values (3,4,'mandy',3)
insert into t values (3,8,'Fisher',4)

output:

familyID memberID firstName memType
----------- ----------- -------------------- -----------
0 7 Stuart 2
0 5 Kasey 3
0 1 Sally 1
0 2 Cooper 4
1 9 Rosemary 1
2 3 Lisa 1
2 6 Stephanie 3
3 4 mandy 3
3 8 Fisher 4

the result I want:

family ID 1 2 3 4 (... 18)
---------- -------- ---------- --------- ------------ -----------
0 Sally Stuart Kasey Cooper
1 Rosemary NULL NULL NULL
2 Lisa NULL Stephanie NULL
3 NULL NULL Mandy Fisher


There are about 18 unique memTypes

Thanks


[quoted text, click to view]



[quoted text, click to view]