Solved

sql query from multiple columns in a row to multiple row per columns

Posted on 2014-09-23
8
210 Views
Last Modified: 2014-09-23
table1 columns,
thedate ,system_id, hour,call type1, call type2, call_type3 from table1
group by thedate, system_id, hour

example values
01/12/14, 1,  1,  25, 13, 1
01/12/14, 1,  1,  20, 11, 0
01/12/14  1,  2, 30,23,15
..
..

From the table above is it possible to create query that will output:

01/12/14 1, 1,  'call type1',  45
01/12/14  1,1,  'call type2',  24
01/12/14   1,1, 'call type3', 1
..
..
0
Comment
Question by:Shen
  • 4
  • 4
8 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40339207
You can't do it with one query but you can do it with one query per column you want to pivot (three in this case) plus a union query to pull them all together into a single recordset.

TheUnionQuery -
Select thedate ,system_id, hour, "Call type 1" AS CallType, [call type1] From YourTable
Union Select thedate ,system_id, hour, "Call type 2" AS CallType, [call type2] From YourTable
Union Select thedate ,system_id, hour, "Call type 3" AS CallType, [call type3] From YourTable;
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40339285
SELECT
    thedate, system_id, hour,
    CASE whichCallType
        WHEN 1 THEN 'call type1'
        WHEN 2 THEN 'call type2'
        WHEN 3 THEN 'call type3' END AS [call type label],
    CASE whichCallType
        WHEN 1 THEN [call type1]
        WHEN 2 THEN [call type2]
        WHEN 3 THEN [call type3] END AS [call type]
FROM (
    SELECT
        thedate, system_id, hour,
        SUM([call type1]) AS [call type1], SUM([call type2]) AS [call type2], SUM([call type3]) AS [call type3]
    FROM table1
    GROUP BY thedate, system_id, hour
) AS derived
CROSS JOIN (
    SELECT 1 AS whichCallType UNION ALL
    SELECT 2 UNION ALL
    SELECT 3
) AS whichCallTypes
ORDER BY thedate, system_id, hour
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40339348
Scott,
My T-SQL is a little spotty.  Does that query write three rows for every "one" row it reads?

Amended suggestion to include summing.

TheUnionQuery -
 Select thedate ,system_id, hour, "Call type 1" AS CallType, Sum([call type1]) As SumCallType From YourTable
Group By thedate ,system_id, hour, "Call type 1"
 Union Select thedate ,system_id, hour, "Call type 2" AS CallType, Sum([call type2]) As SumCallType From YourTable
thedate ,system_id, hour, "Call type 2"
 Union Select thedate ,system_id, hour, "Call type 3" AS CallType, Sum([call type3]) As SumCallType From YourTable
thedate ,system_id, hour, "Call type 3";
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40339372
@Pat:
Yes.  The CROSS JOIN will join every row in the first table (our table input, in this case) and join it to every row in the second table (numbers 1, 2 and 3 in this case).  Thus, 3 rows are output for every row in the main input table.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 34

Expert Comment

by:PatHartman
ID: 40339396
Thanks,
Access SQL hasn't changed in over 20 years (except to add support for the abomination data types) so I forget what is possible when you step out into the real world.  I still like my curmudgeon approach though since it works in Jet/ACE as well as SQL Server.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40339414
You could still do that in Access.  Just do an INNER JOIN ON 1 = 1, which would have the same affect as a CROSS JOIN.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40339435
Huh?  That query doesn't even sum up the values as originally requested.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40340119
It looks like I flunked cut and paste.  Here it is - one last time.

TheUnionQuery -
Select thedate ,system_id, hour, "Call type 1" AS CallType, Sum([call type1]) As SumCallType
From YourTable
Group By thedate ,system_id, hour, "Call type 1"

Union Select thedate ,system_id, hour, "Call type 2" AS CallType, Sum([call type2]) As SumCallType
From YourTable
Group By thedate ,system_id, hour, "Call type 2"

Union Select thedate ,system_id, hour, "Call type 3" AS CallType, Sum([call type3]) As SumCallType
From YourTable
Group By thedate ,system_id, hour, "Call type 3";
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now