?
Solved

sql query that can switch rows to columns

Posted on 2012-12-28
4
Medium Priority
?
139 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
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

580 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