August 7, 2003 at 6:23 am
If I have a table like this:
ID Comp Value
---------------------
1 1 4
1 2 7
1 3 3
2 1 10
2 3 2
I would like a query that returns this:
ID 1 2 3
----------------------
1 4 7 3
2 10 2
Note I don't know beforehand how many different comp values there will be.
My brain has been wrestling with this, but I can't see the light.
Thanks
August 7, 2003 at 6:48 am
Have a look at this:
http://qa.sqlservercentral.com/faq/viewfaqanswer.asp?faqid=206
The downside is that you can only return one value at a time but if you put it code into a function then you can get it in all one statment.
Jeremy
August 7, 2003 at 7:52 am
Here's a solution using dynamic sql
declare @max int
declare @ct int
declare @sql nvarchar(250)
select @max = max(Comp) from tablea
create table #newtable ([ID] int)
set @ct=0
while (@ct < @max)
begin
set @ct=@ct+1
set @sql = 'alter table #newtable add _'+cast(@ct as varchar)+ ' int default 0'
exec sp_executesql @sql
end
insert into #newtable ([ID]) select distinct [ID] from tablea order by [ID]
set @ct=0
while (@ct < @max)
begin
set @ct=@ct+1
set @sql = 'update n set n._'+cast(@ct as varchar)+' = a.Value from #newtable n inner join tablea a on a.ID = n.ID and a.Comp = '+cast(@ct as varchar)
exec sp_executesql @sql
end
select * from #newtable
Far away is close at hand in the images of elsewhere.
Anon.
August 7, 2003 at 7:57 am
I forgot to mention that the heading of the result also comes from the table. So the different values from the comp column must result in a column for each, hence the 1, 2 and 3 in the heading. It would of course have been easier to spot if I had used a textvalue instead.
August 7, 2003 at 8:07 am
And finally I forgot to say that the comp column is not sequential in anyway. So the values 1, 2, 3 should have been "more" random.
August 7, 2003 at 8:07 am
In that case can you post the table definition and some sample data in that format and is there a maximum number of 'Comp' values?
Far away is close at hand in the images of elsewhere.
Anon.
August 7, 2003 at 8:52 am
With a slightly changed schema and data:
ID sampleindex comp value
-------------------------
1 1 100 4
2 1 200 7
3 1 300 3
4 1 100 10
5 1 300 2
With the help from those that answered my original
question I've managed to scrape together the following
stored procedure that does the trick:
-------------------------------------------------------
CREATE PROCEDURE testsp AS
DECLARE @sql nvarchar(250)
-- insert all unique CIDs into temporary table:
create table #newtable ([CID] int)
INSERT INTO #newtable SELECT DISTINCT CID FROM table1 ORDER BY [CID]
-- debug select
-- select * from #newtable
-- now loop through CIDs and append column names to temporary result table:
create table #resulttable ([sampleindex] int)
-- define static (forward only) cursor:
DECLARE mycursor CURSOR STATIC FOR SELECT CID FROM #newtable
DECLARE @CID int
-- open cursor
OPEN mycursor
FETCH NEXT FROM mycursor INTO @CID
WHILE @@FETCH_STATUS=0
BEGIN
set @sql = 'alter table #resulttable add _'+cast(@CID as varchar)+ ' int default null' exec sp_executesql @sql
FETCH NEXT FROM mycursor INTO @CID
END
-- remember to close the thing
CLOSE mycursor
-- and deallocate it:
DEALLOCATE mycursor
-- debug select
-- select * from #resulttable
-- insert empty records into temporary resulttable:
insert into #resulttable ([sampleindex]) select distinct [sampleindex] from table1 order by [sampleindex]
-- debug select
-- select * from #resulttable
-- now loop through CIDs and update their values in the temporary resulttable
-- define static (forward only) cursor:
DECLARE mycursor CURSOR STATIC FOR SELECT CID FROM #newtable
-- open cursor
OPEN mycursor
FETCH NEXT FROM mycursor INTO @CID
WHILE @@FETCH_STATUS=0
BEGIN
set @sql = 'update rt set rt._'+cast(@CID as varchar)+' = t1.Value from #resulttable rt inner join table1 t1 on t1.sampleindex = rt.sampleindex and t1.CID = '+cast(@CID as varchar) exec sp_executesql @sql
FETCH NEXT FROM mycursor INTO @CID
END
-- remember to close the thing
CLOSE mycursor
-- and deallocate it:
DEALLOCATE mycursor
-- output the result
SELECT * FROM #resulttable
GO
----------------------------------------------------------
However I don't like the use of cursors and temporary
tables that much, but perhaps there are no other way??????
Anyway thanks to everybody that answered.
August 8, 2003 at 7:26 am
quote:
If I have a table like this:ID Comp Value
---------------------
1 1 4
1 2 7
1 3 3
2 1 10
2 3 2
I would like a query that returns this:
ID 1 2 3
----------------------
1 4 7 3
2 10 2
Note I don't know beforehand how many different comp values there will be.
My brain has been wrestling with this, but I can't see the light.
Thanks
August 8, 2003 at 7:28 am
You also can try:
select id ,
max(case comp when '1' then value else ' ' end) '1',
max(case comp when '2' then value else ' ' end) '2',
max(case comp when '3' then value else ' ' end) '3'
from h group by id
It returns:
id 1 2 3
----------- ----------- ----------- -----------
1 4 7 3
2 10 0 2
(2 row(s) affected)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply