Solved

Change Columns into rows

Posted on 2013-02-03
6
231 Views
Last Modified: 2013-02-03
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
0
Comment
Question by:skull52
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 38849332
SELECT ITEMNMBR, MAX_QTY AS MAX_MIN_QTY
FROM SomeTable
UNION ALL
SELECT ITEMNMBR, MIN_QTY AS MAX_MIN_QTY
FROM SomeTable
ORDER BY 1 ASC, 2 DESC
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38849334
Or to show on each row whether it's min or max...

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
0
 
LVL 9

Expert Comment

by:Aeriden
ID: 38849351
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
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 74

Expert Comment

by:sdstuber
ID: 38849380
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
0
 

Author Comment

by:skull52
ID: 38849753
Thanks Matthew that worked perfectly, and thanks to all those that responded .
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 38849780
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
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

Open in new window

 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           

Open in new window

0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question