Avatar of CASorter
CASorter
Flag for United States of America asked on

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
Microsoft SQL Server

Avatar of undefined
Last Comment
CASorter

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Louis01

Use the PIVOT function.
CASorter

ASKER
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..
CASorter

ASKER
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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck