Fred Webb
asked on
Change Columns into rows
I have a table consisting of 3 columns ITEMNMBR, MAX QTY, MIN QTY I want the the Max and Min to display as rows instead of columns
Current results
ITEMNMBR MAX QTY MIN QTY
-------------------------- ---- -------------------------- ---------- ---------- ---- -------------------------- --------
ATI47BB 50 5
ATIAOPS 50 5
ATIARWRENCH 50 5
Desired Results
ITEMNMBR MAX_MIN QTY
-------------------------- ---- -------------------------- ---------- ---------- ----
ATI47BB 50
ATI47BB 5
ATIAOPS 50
ATIAOPS 5
ATIARWRENCH 50
ATIARWRENCH 5
Current results
ITEMNMBR MAX QTY MIN QTY
--------------------------
ATI47BB 50 5
ATIAOPS 50 5
ATIARWRENCH 50 5
Desired Results
ITEMNMBR MAX_MIN QTY
--------------------------
ATI47BB 50
ATI47BB 5
ATIAOPS 50
ATIAOPS 5
ATIARWRENCH 50
ATIARWRENCH 5
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here is one potential approach that may work for you if have more complex requirements than unions (the other options are better, but thought to share this approach, just in case a more "manual" approach was warranted)...
/* Change the @ variables size/type to match itemnmbrs size/type */
declare @itemnmbr char(11)
declare @maxqty int
declare @minqty int
/* replace table1 name with the actual old table name */
select @itemnmbr = min(itemnmbr) from table1
while @itemnmbr is not null
begin
/* use the actual variable names for maxqty, minqty from table1 */
select @maxqty = maxqty, @minqty = minqty from table1 where itemnmbr = @itemnmbr
/* replace table2 anme with the actual new table name, min_max_qty in the new table */
insert into table2 (itemnmbr, min_max_qty) values (@itemnmbr, @maxqty)
insert into table2 (itemnmbr, min_max_qty) values (@itemnmbr, @minqty)
select @itmnmbr = min(itemnmbr) from table1 where itemnmbr > @itemnmbr
end
/* Change the @ variables size/type to match itemnmbrs size/type */
declare @itemnmbr char(11)
declare @maxqty int
declare @minqty int
/* replace table1 name with the actual old table name */
select @itemnmbr = min(itemnmbr) from table1
while @itemnmbr is not null
begin
/* use the actual variable names for maxqty, minqty from table1 */
select @maxqty = maxqty, @minqty = minqty from table1 where itemnmbr = @itemnmbr
/* replace table2 anme with the actual new table name, min_max_qty in the new table */
insert into table2 (itemnmbr, min_max_qty) values (@itemnmbr, @maxqty)
insert into table2 (itemnmbr, min_max_qty) values (@itemnmbr, @minqty)
select @itmnmbr = min(itemnmbr) from table1 where itemnmbr > @itemnmbr
end
if you don't want to query your table multiple times...
select itemnbr, case when n = 1 then max_qty else min_qty end as max_min
from yourtable, (select 1 n union all select 2 n) as x
or, to include the max/min flag as shown above....
select itemnbr, case when n = 1 then max_qty else min_qty end as max_min,
case when n= 1 then 'Max' else 'Min' end as max_min_type
from yourtable, (select 1 n union all select 2 n) as x
select itemnbr, case when n = 1 then max_qty else min_qty end as max_min
from yourtable, (select 1 n union all select 2 n) as x
or, to include the max/min flag as shown above....
select itemnbr, case when n = 1 then max_qty else min_qty end as max_min,
case when n= 1 then 'Max' else 'Min' end as max_min_type
from yourtable, (select 1 n union all select 2 n) as x
ASKER
Thanks Matthew that worked perfectly, and thanks to all those that responded .
You can use a Common Table Expression (CTE) to help reduce the query overhead.
Using "union all" rather than "union" ensures no rows get suppressed (I assume that's what you want).
You might also want an indicator of 'min' or 'max' (or something similar) to help sort the output
Using "union all" rather than "union" ensures no rows get suppressed (I assume that's what you want).
You might also want an indicator of 'min' or 'max' (or something similar) to help sort the output
with src_data as (
/* place query for your source data here
e.g.
select * from my_table
where something...
*/
)
select itemnmbr, max_qty as max_min_qty, 'max' as min_or_max
from src_data
union all
select itemnmbr, min_qty as max_min_qty, 'min' as min_or_max
from src_data
order by itemnmbr, min_or_max
ITEMNMBR MAX_MIN_QTY MIN_OR_MAX
----------- -------------- -------------
ATI47BB 50 max
ATI47BB 5 min
ATIAOPS 50 max
ATIAOPS 5 min
ATIARWRENVH 50 max
ATIARWRENVH 5 min
SELECT ITEMNMBR, 'Max' AS MIN_MAX, MAX_QTY AS MAX_MIN_QTY
FROM SomeTable
UNION ALL
SELECT ITEMNMBR, 'Min' AS MIN_MAX, MIN_QTY AS MAX_MIN_QTY
FROM SomeTable
ORDER BY 1 ASC, 2 DESC