CODEWITHSUNDEEP
sqlrows
eatonphil
eatonphil

Reputation: 13702

SQL - transforming rows without the use of pivot, etc

I need some help organizing a table for a report I must create. Unfortunately the layout of the table is quite difficult to work with. I am aware of the pivot function but it will not work in this case because I am using Intersystems' Cache SQL. I am not marketing this problem as that kind though because the aid-base for Cache SQL is very small. If you can solve it in your language it will help me in mine. Here is the table:

PID    DE_Num   DE_Val
1001   102    arbitrary value
1001   650    arbitrary value
1001   6225    10/12/12
1001   6227    01/14/09
1343   809    arbitrary value
1343   103    arbitrary value
1343   6225    11/23/11
1343   412    arbitrary value
1343   6227    03/07/01
1222   6225    05/19/12
1222   6227    08/08/08

I need the outcome table to look like this:

PID    DE_Num    DE_Val
1001    6225    10/12/12
1001    6227    01/14/09
1343    6225    11/23/11
1343    6227    03/07/01
1222    6225    05/19/12
1222    6227    08/08/08

Any suggestions? Let me know if you need more info. Again I do not need Cache SQL specific info. Information in general is just as great a help!

Upvotes: 0

Views: 302

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95722

Well, it's not really hard to produce that output.

select *
from your_table_name
where DE_Num > 6000
order by PID, DE_Num

I'll be pretty surprised if that's what you're looking for, though. I see nothing in your question that suggests you need a PIVOT, or anything remotely like a PIVOT.

Upvotes: 1

Related Questions