sql query that can switch rows to columns

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
CASorterAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Louis01Commented:
Use the PIVOT function.
0
 
CASorterAuthor Commented:
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
 
CASorterAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.