multiple recors to one row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

multiple recors to one row

Author

Topic

Caitlin11
Starting Member

1 Post

Posted - 2009-05-07 : 17:01:35

Hi - I'm thinking there has to be a more efficient way to accomplish what I'm trying to do. I have a "details" table that has rows like this:

ID item pc
1234 001 54678
1234 002 67894
5678 001 65577
....

I have a query that is producing the following:
ID pc1 pc2 pc3
1234 54678 67894
5678 65577

Which is exactly what I want it to produce. The query I'm using to do this is:
select ID,
Min(case when item = '001' then pc else null end) as pc1,
Min(case when item = '002' then pc else null end) as pc2,
Min(case when item = '003' then pc else null end) as pc3
from details
group by claimno

The problem is I have to account for 45 items which would mean adding 42 "Case" statements to the above - AND I have 9 other fields that I want to pull like this from table.details to one row.

so - Is there a more efficient way to do this? Maybe a cursor? Any ideas would be greatly appreciated.

Thanks!

dsindo
Starting Member

45 Posts

Posted - 2009-05-07 : 19:20:56

CREATE FUNCTION getpc_value
(
@id varchar(100)
)
RETURNS Varchar(8000)
AS
BEGIN
DECLARE @Ret varchar(8000)
SELECT @Ret=COALESCE(@Ret+',','') + cast(pc as varchar(10)) FROM details
WHERE id=@id
RETURN @Ret
END

SELECT DISTINCT id, dbo.getpc_value(id)
FROM details

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources