• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

SQL 2008 query to change from tows to columns

I would like to change the output from rows to columns based on the following query:

use StyleResearch
select Product_ID as 'Product Name',value as Value,itemname as Statastic from dbo.tblStyleResearchExtract_Inbound
where Trail_Name='600421 Broad 3' and datadate =201012 and TypeID=14 and itemid in (1,3,5)

Product Name      Value      Statastic
600421      5.016596      Tracking Error
600421      32      Port - Num of Stocks
600421      28.77415      Port - Effective Num of Stocks

I would like it to look like:

Product Name      Tracking Error      Port -Num of Stocks      Port - Effective Num of Stocks
600421      5.016596      32      28.77415
0
majervis
Asked:
majervis
  • 3
  • 2
1 Solution
 
SharathData EngineerCommented:
Are the number of Statastics 3 every time?
0
 
kamindaCommented:
You can use PIVOT to achieve this
SELECT [Product_ID] , 
[Tracking Error], [Port - Num of Stocks], [Port - Effective Num of Stocks]
FROM
dbo.tblStyleResearchExtract_Inbound
PIVOT
(
SUM(Value)
FOR itemname IN ([Tracking Error], [Port - Num of Stocks], [Port - Effective Num of Stocks])
) AS PivotTable;

Open in new window

0
 
majervisAuthor Commented:
quick follow on question.  Where would I put in a condition "where 'trail_name' like '%broad 3%. "?

I don't need to see trail_name, but I want to filter on it.  Many thanks.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SharathData EngineerCommented:
like this.
SELECT [Product Name], 
       [Tracking Error], 
       [Port - Num of Stocks], 
       [Port - Effective Num of Stocks] 
  FROM (SELECT [Product Name], 
               VALUE, 
               Statastic 
          FROM dbo.tblStyleResearchExtract_Inbound 
         WHERE trail_name LIKE '%broad 3%') t1 
       PIVOT 
       (SUM(VALUE) 
        FOR Statastic IN ( [Tracking Error],[Port - Num of Stocks],[Port - Effective Num of Stocks] ) ) AS PivotTable; 

Open in new window

0
 
majervisAuthor Commented:
One more tweak and thanks for all your help.  I have a field called datadate that i need to have included.

on each row, there should be product name, datadate,tracking error, port - num of stocks and Port - Effective Num of Stocks.  I have tried to edit your code, but I am not well versed in how to edit SQL.  Many thanks in advance.
0
 
SharathData EngineerCommented:
SELECT [Product Name],
       datadate,
       [Tracking Error],
       [Port - Num of Stocks],
       [Port - Effective Num of Stocks]
  FROM (SELECT [Product Name],
               VALUE,
               datadate,
               Statastic
          FROM dbo.tblStyleResearchExtract_Inbound
         WHERE trail_name LIKE '%broad 3%') t1
       PIVOT
       (SUM(VALUE)
        FOR Statastic IN ( [Tracking Error],[Port - Num of Stocks],[Port - Effective Num of Stocks] ) ) AS PivotTable;
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.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now