Solved

sql query that can switch rows to columns

Posted on 2012-12-28
4
125 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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
Comment Utility
Use the PIVOT function.
0
 

Author Comment

by:CASorter
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

728 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

8 Experts available now in Live!

Get 1:1 Help Now