Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help in converting rows into column... include attached image file in detail Expand / Collapse
Author
Message
Posted Friday, April 18, 2014 5:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 16, 2014 3:09 PM
Points: 10, Visits: 51
Team,

It is possible to covert rows into column by using tsql script, please see attached file for more details, please provide me tsql script.

THANK YOU IN ADVANCE.


CREATE TABLE Table1 (SalesOrder varchar(10), ItemName VARCHAR(100), Price INT, ItemNo int)
GO

INSERT INTO Table1
SELECT '01', 'Camera', 100, 1
UNION ALL
SELECT '01', 'Memory 4GB', 10, 2
UNION ALL
SELECT '01', 'Battery', 5, 3
UNION ALL
SELECT '02', 'Keyboad', 10, 1
UNION ALL
SELECT '02', 'Mouse', 05, 2
UNION ALL
SELECT '03', 'CPU', 300, 1
UNION ALL
SELECT '03', 'Motherboad', 400, 2
GO

SELECT SalesOrder, ItemName, Price, ItemNo
FROM Table1
GO


  Post Attachments 
pivotout.jpg (22 views, 77.45 KB)
Post #1563205
Posted Friday, April 18, 2014 10:17 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 745, Visits: 4,778
Are you doing this in T-SQL or in Reporting Services?

if you're using SSRS, use a Matrix. Otherwise, read this:
Using PIVOT and UNPIVOT
Post #1563209
Posted Friday, April 18, 2014 11:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 16, 2014 3:09 PM
Points: 10, Visits: 51
im doing in tsql, can you provide me tsql statement, thank you
Post #1563214
Posted Friday, April 18, 2014 11:37 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 745, Visits: 4,778
No. do your own work. Read the article, try it, and post your SQL if you get stuck.
Post #1563216
Posted Saturday, April 19, 2014 2:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 7,161, Visits: 13,229
If you have trouble getting the PIVOT syntax right, you might want to try the "classic" rossTab approach. alternatively.

If the max. number of items is known (e.g. 3 based on your sample data), you wouldn't need the dynamic version.
But if the max number can vary, either write the CrossTab to cover a max limit you're safe with or have a look at the DynamicCrossTab solution.
Both articles are referenced in my signature.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1563218
Posted Sunday, April 20, 2014 4:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 1,909, Visits: 19,123
I think that you may have oversimplified your real data....??
do you know in advance the maximum number of items that are allowed?

anyways here is some code that does what you asked for, based on the limited sample data provided.
it uses the concept in these two fine articles (as Lutz suggested)

http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/T-SQL/63681/

assuming you did read these...then maybe you are struggling in having to do two separate pivots and rejoin them to meet your specific layout requirements.......



;with cte as
(
SELECT SalesOrder,
max(case when itemno =1 then itemname else '' end) as item1,
max(case when itemno =2 then itemname else '' end) as item2,
max(case when itemno =3 then itemname else '' end) as item3,
rn =1
FROM Table1
GROUP BY SalesOrder
UNION ALL
SELECT SalesOrder,
max(case when itemno =1 then cast(price as varchar) else '' end) as item1,
max(case when itemno =2 then cast(price as varchar) else '' end) as item2,
max(case when itemno =3 then cast(price as varchar) else '' end) as item3,
rn =2
FROM Table1
GROUP BY SalesOrder
)

SELECT salesorder,
item1,
item2,
item3
FROM cte
ORDER BY salesorder, rn






______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1563320
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse