Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL 2008 query to change from tows to columns

Posted on 2011-05-06
6
Medium Priority
?
290 Views
Last Modified: 2012-05-11
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
Comment
Question by:majervis
  • 3
  • 2
6 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 35707691
Are the number of Statastics 3 every time?
0
 
LVL 9

Expert Comment

by:kaminda
ID: 35707912
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
 

Author Comment

by:majervis
ID: 35708198
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 41

Expert Comment

by:Sharath
ID: 35708450
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
 

Author Comment

by:majervis
ID: 35709191
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
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 35709574
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Screencast - Getting to Know the Pipeline

810 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