Convert block of Rows to columns in SQL 1

[cariaco99]

Hi there.

I have a table with the following record structure:

ID TheGroup TheValue TheSubGroup
430093 47 MARQUEZ 1
430094 47 RAFAEL 1
430095 47 MIAMI 1
430096 47 FL 1
430097 47 33172 1
430113 47 MOORE 2
430114 47 ROSA 2
430095 47 NAPLES 2
430096 47 FL 2
430097 47 32019 2
.....
..... and so on

and I would like to get a subset of records looking like this:

1 MARQUEZ RAFAEL MIAMI FL 33172
2 MOORE ROSA NAPLES FL 32019
.....
..... etc.

I do not know the number of rows that contains one 'blocks of records'
but I can limit it to a number lesser than 10 or 5 for that matter.

I am using MS SQL, but I expect it (I hope) to make it work in Oracle or DB2 also

Help will be much appreciated...

Thanks

 

[dnoeth]

Oracle/DB2:
2004-01-28, "Unusual one-to-many relationship problem."
thread220-761588 SQL:
2004-01-10, "TSQL , SQL/Server/2000 - Change a Verticle record to a Horizontal"
thread220-746454

 

[cariaco99]

Thanks for the help, dnoeth, but your solution doesn't bring back the rows with 5 (or any number) columns, but it returns 1 concatenated column with all the results inside.

Any thougts?

Your help is much appreciated....

 

[dnoeth]

Hmmmmmm, how about removing the concat operator with a column delimiter?
Check the first link again...

Dieter

 

[cariaco99]

dnoeth:

another member (nigelrivett) sent me this code, which returns the correct columns, but now it returns the ENTIRE table times the number of correct rows!:
This is confusing (and I still haven't put inside the neccesary 'WHERE' to filter the answers, because the query should return in the end, for instance, only the resulting records with let's say 'MIAMI', but that is another story (sob))

This is the code:

select ResponseID ,
(select top 1 Answer from (select top 1 id,Answer from tbl_SurveyResponses t2 where t2.ResponseID = t1.ResponseID and t2.SurveyID=47 order by id) a order by id desc) ,
(select top 1 Answer from (select top 2 id,Answer from tbl_SurveyResponses t2 where t2.ResponseID = t1.ResponseID and t2.SurveyID=47 order by id) a order by id desc) ,
(select top 1 Answer from (select top 3 id,Answer from tbl_SurveyResponses t2 where t2.ResponseID = t1.ResponseID and t2.SurveyID=47 order by id) a order by id desc) ,
(select top 1 Answer from (select top 4 id,Answer from tbl_SurveyResponses t2 where t2.ResponseID = t1.ResponseID and t2.SurveyID=47 order by id) a order by id desc) ,
(select top 1 Answer from (select top 5 id,Answer from tbl_SurveyResponses t2 where t2.ResponseID = t1.ResponseID and t2.SurveyID=47 order by id) a order by id desc)
from tbl_SurveyResponses t1
WHERE t1.SurveyID=47
group by ResponseID
order by ResponseID

This is almost what I am looking for.

Thanks in advanced.

 

[nigelrivett]

You will come up against the problem with accessing derived tables with a nested subquery in the select which would cure the problem - so maybe something like

select t.ResponseID ,
t1.Answer ,
t2.Answer ,
t3.Answer ,
t4.Answer ,
t5.Answer
from
(select distinct ResponseID from tbl_SurveyResponses WHERE SurveyID=1) t
join (select ResponseID, Answer, rownum = (select count(*) from tbl_SurveyResponses t2 where t2.ResponseID = t1.ResponseID and t2.SurveyID=t1.SurveyID and t2.id <= t1.id)
from tbl_SurveyResponses t1) t1 on t.ResponseID = t1.ResponseID and t1.rownum = 1
join (select ResponseID, Answer, rownum = (select count(*) from tbl_SurveyResponses t2 where t2.ResponseID = t1.ResponseID and t2.SurveyID=t1.SurveyID and t2.id <= t1.id)
from tbl_SurveyResponses t1) t2 on t.ResponseID = t2.ResponseID and t2.rownum = 2
join (select ResponseID, Answer, rownum = (select count(*) from tbl_SurveyResponses t2 where t2.ResponseID = t1.ResponseID and t2.SurveyID=t1.SurveyID and t2.id <= t1.id)
from tbl_SurveyResponses t1) t3 on t.ResponseID = t3.ResponseID and t3.rownum = 3
join (select ResponseID, Answer, rownum = (select count(*) from tbl_SurveyResponses t2 where t2.ResponseID = t1.ResponseID and t2.SurveyID=t1.SurveyID and t2.id <= t1.id)
from tbl_SurveyResponses t1) t4 on t.ResponseID = t4.ResponseID and t4.rownum = 4
join (select ResponseID, Answer, rownum = (select count(*) from tbl_SurveyResponses t2 where t2.ResponseID = t1.ResponseID and t2.SurveyID=t1.SurveyID and t2.id <= t1.id)
from tbl_SurveyResponses t1) t5 on t.ResponseID = t5.ResponseID and t5.rownum = 5
order by t.ResponseID

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[cariaco99]

IT WORKED!!!!!!!!!!
I added some extra filters to your query, but it worked at the third try and it's fast!
Thanks!