April 18, 2014 at 5:11 pm
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
April 18, 2014 at 10:17 pm
Are you doing this in T-SQL or in Reporting Services?
if you're using SSRS, use a Matrix. Otherwise, read this:
April 18, 2014 at 11:24 pm
im doing in tsql, can you provide me tsql statement, thank you
April 18, 2014 at 11:37 pm
No. do your own work. Read the article, try it, and post your SQL if you get stuck.
April 19, 2014 at 2:35 am
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.
April 20, 2014 at 4:00 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply