Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql query that can switch rows to columns

Posted on 2012-12-28
4
Medium Priority
?
135 Views
Last Modified: 2013-01-02
i have a query that gives me orientation one way and i need it the other.

like

co1  widg1 1321  12/4/12 3
co1  widg2 1322  12/4/12 2
co1  widg1  1321 12/11/12 1
co1  widg1  1321 12/18/12 1
co1  widg2  1322 12/18/12 4
co1  widg2  1322 12/25/12 2
co1  widg1  1321 12/25/12 3


i need it like
                             12/4/12       12/11/12      12/18/12    12/25/12
co1   widg1  1321     3                   1                   1                  2
co1   widg2  1322     2                   0                   4                  3


i could do it brute force in a program, but if i could get a query to feed it to me in the orentation i need that would be great.

i am feeling it might have something to do with the PIVOT

here is the query i am using

SELECT     CT.CTCoName, CTDet.CTDetDesc, CTDet.CTDetProd, CT.CTDate, COUNT(*) AS Expr1
FROM         CT INNER JOIN
                      CTDet ON CT.CTID = CTDet.CTID
WHERE     (CTDet.CTDetSorted IS NOT NULL)
GROUP BY CTDet.CTDetDesc, CTDet.CTDetProd, CT.CTDate, CT.CTCoName
HAVING      (CTDet.CTDetProd LIKE '%widg%') AND (CT.CTDate > CONVERT(DATETIME, '2012-10-02 00:00:00', 102)) AND
                      (CT.CTCoName = 'co1')
ORDER BY CT.CTDate, CT.CTCoName
0
Comment
Question by:CASorter
[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
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38727651
dynamic crosstab is the answer here:
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx
you need to work out the actual sql to run (aka the parameters to pass), but otherwise it's fairly simple
0
 
LVL 11

Expert Comment

by:Louis01
ID: 38728807
Use the PIVOT function.
0
 

Author Comment

by:CASorter
ID: 38732946
um, ya   that is what i thought..  

ok, lets ask the question.

how would i do the pivot on the above query to get the results i am looking for.

i am trying to wedge my query into angels' response.. with little success.  
but i shall continue trying..
0
 

Author Closing Comment

by:CASorter
ID: 38736911
fyi...   i had to do the pivot on the date column.

it took some tweakin to get it to sort in numerical order,   it kept saving the pivot table in mon day year  HH:MM:SSPM  format, so when it sorted, it sorted in month name alphabetical  so december came in front of november....   after i got that straightened it worked well.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

730 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