SQL Sort/Paging question Question

Question

Lets say I have a pivoted sorted dataset like this

ID    Col1    Col2
1     a       11
2     b       22
3     c       33
4     d       44
5     e       55

When I make a paging call by returning two records at a time I would get the first two rows.

Lets say I want to return the same data but not pivot the data so my data set looks like

ID    Col     Val
1     Col1    a 
2     Col1    b
3     Col1    c
4     Col1    d
5     Col1    e
1     Col2    11
2     Col2    22 
3     Col2    33
4     Col2    44 
5     Col2    55

I would like to write an sql statement that would return the same data as in the first example but without pivoting the data first.

Some additional challanges

1) There could be n columns not just two

2) Tt should also support a filter on all the columns. This part I have solved already see below

Filter on pivoted data
WHERE Col1 in ('a', 'b', 'c')
AND Col2 in ('11', '22')

Filter on unpivoted data
WHERE (Col = 'Col1' and Val in ('a', 'b', 'c')) or Col != 'Col1') 
AND (Col = 'Col2' and Val in ('11', '22')) or Col != 'Col2') 

Both filters return the same results.

The filter part I have figured out already I am stuck on the sorting and paging.

One option that I am looking at is to use ROW_NUMBER and then sort and page on that. — John Soer, Aug 06 '11 at 04:28

score 1 · Answer 1 · answered Aug 06 '11 at 04:35

SQL, as a standard, doesn't support such operations. If you want it to handle arbitrarily many columns for your reformatting of the data, then use something like Perl's DBI interface which can tell you the names of the columns for any table. From there you can generate your table create.

To create your second table the insert will take the form:

INSERT INTO newtable (id, col, val)
SELECT id, 'Col1', Col1 from oldtable
UNION
SELECT id, 'Col2', Col2 from oldtable;

Just create an additional UNION SELECT... for each column you want to include.

As for you filter query, you're making it unnecessarily complicated. Your query of:

SELECT * FROM newtable
WHERE (Col = 'Col1' and Val in ('a', 'b', 'c')) or Col != 'Col1') 
AND (Col = 'Col2' and Val in ('11', '22')) or Col != 'Col2')

Can be rewritten as

SELECT * from newtable
WHERE ( Col = 'Col1' and Val in ('a','b','c') )
   OR ( Col = 'Col2' and Val in ('11','22')   )

Each separate ORd clause doesn't interfere with the others.

I also don't understand why people try to work such travesties in SQL. It appears that you're trying to make a reasonable schema into something akin to a key/value store. Which may currently be all the rage with the kids nowadays, but you should really try to learn how to use the full power of SQL with good data modeling.

SQL Sort/Paging question Question

1 Answers1