Converting Rows into Columns 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 MONTREAL 1
430096 47 CANADA 1
430097 47 AMERICA 1
430113 47 MOORE 2
430114 47 ROBERT 2
430095 47 NAPLES 2
430096 47 FLORIDA 2
430097 47 AMERICA 2
.....
..... and so on

and I would like to get a subset of records looking like this
(grouped by TheSubGroup):

1 MARQUEZ RAFAEL MONTREAL CANADA AMERICA
2 MOORE ROBERT NAPLES CANADA AMERICA
.....
..... etc.

I do not know the number of rows that contains one 'blocks of records', but I do know that the returned subset WILL have the same number of rows per block

I can also 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

 

[nigelrivett]

Something like

select subgroup ,
(select top 1 value from (select top 1 id, value from tbl t2 where t2.subgroup = t1.subgroup order by id) a order by id desc) ,
(select top 1 value from (select top 2 id, value from tbl t2 where t2.subgroup = t1.subgroup order by id) a order by id desc) ,
(select top 1 value from (select top 2 id, value from tbl t2 where t2.subgroup = t1.subgroup order by id) a order by id desc) ,
...
from tbl t1
group by subgroup
order by subgroup

If you want an indeterminate number of values then do it in dynamic sql.

======================================
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]

Thanks, Nigel.
I almost got it!, but I don't know what I did wrong when translating your code into my real situation.

I modified (just added a 'where' filter)

select ResponseID ,
(select top 1 Answer from (select top 1 id, Answer from tbl 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 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 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 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 t2 where t2.ResponseID = t1.ResponseID and t2.SurveyID=47 order by id) a order by id desc)
from tbl t1
WHERE t1.SurveyID=47
group by ResponseID
order by ResponseID

I have 128754 records in my table, of which 10 records (2 blocks of 5 rows) are supposed to be filtered by
-> WHERE t1.SurveyID=47
-> and t2.SurveyID=47
and expected to retrieve 2 records

my results were like this:

the query retrieved 257508 records, (the total records times 2)
of these records, the first 104 records are PERFECT (just duplicated 104 times), then the next 104 records are the EXPECTED 2 records duplicated also, and then I got a zillion NULLs

I'm baffled, but I feel I'm close!

Thanks in advance

 

[nigelrivett]

This is a slight buglet in t-sql - I have posted a workaround in the ansi sql thread.

if anyone's interested it's associated with this problem (which would be the fix).
It gives an error because it can't resolve t1.id in the nested subquery

select id ,
(select top 1 name from (select top 1 name from sysobjects t2 where t1.id = t2.id) a)
from (select id from sysobjects) t1

This though is OK
select id ,
(select top 1 name from sysobjects t2 where t1.id = t2.id)
from (select id from sysobjects) t1

You will see that this gives the same problem as in my failed solution above
select id ,
(select top 1 name from (select top 1 name from sysobjects t2 where t1.id = t2.id) a)
from syscolumns t1
group by id

whereas this is ok but not what is needed
select id ,
(select top 1 name from sysobjects t2 where t1.id = t2.id) a
from syscolumns t1
group by id


======================================
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.