I'm looking for way to convert rows to columns in SQL server 2012

Question

0.00/5 (No votes)

See more:

This is my example:

Date        Itemname      qty     price 
01/09/2016  ABC           100     1000
02/09/2016  PQR           200     500
02/09/2016  ABC           50      500
04/09/2016  XYZ           10      100
05/09/2016  ABC           50      500

I want result:

Date             ABC           PQR           XYZ
             qty  price     qty  price      qty  price

01/09/2016   100  1000      
02/09/2016   50   500       200   500       
04/09/2016   50   100                       10    100

Item names are not fix.I have an unknown number of Item names.

What I have tried:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName) 
                    from yourtable
                    group by ColumnName, id
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select value, ColumnName
                from yourtable
            ) x
            pivot 
            (
                max(value)
                for ColumnName in (' + @cols + N')
            ) p '

exec sp_executesql @query;

Posted 20-Sep-16 2:11am

Member 12749874

Updated 21-Sep-16 5:42am

Wendelius

v3

Add a Solution

Comments

Karthik_Mahalingam 20-Sep-16 9:04am

post your expected result in table format. its not clear.

2 solutions

Solution 1

Pivot is probably what youa re looking for:
Simple Way To Use Pivot In SQL Query[^]
PIVOT and UNPIVOT in Sql Server | SqlHints.com[^]
Using PIVOT and UNPIVOT[^]

Posted 20-Sep-16 2:27am

Patrice T

Comments

Member 12749874 20-Sep-16 8:50am

Its work only when i have fix number columns.
But I have no fix number columns.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2016-09-21T05:42:00

Following on from Solution 1 and your comment to it...

The first thing to do is to get the results into the format you require for the fixed data you have shown in your example.

One way of doing that would be to UNPIVOT your data first and then PIVOT using a dummy column for the qty and price for each ItemName e.g.

SQL

;with CTE as
(
	SELECT * FROM 
		(SELECT [Date], qty, price, itemname
		FROM yourTable) p
		UNPIVOT
	   (value FOR what IN (qty, price)
	)AS unpvt
)
select [Date],	ABCqty,ABCprice,
		PQRqty,PQRprice,
		XYZqty,XYZprice
from
(
	select [Date],value, ItemName + what as itemwhat
	from CTE
) qry
pivot 
(
	max(value)
    for itemwhat in ([ABCqty],[PQRqty],[XYZqty],[ABCprice],[PQRprice],[XYZprice])
) as pvt
order by [Date]

Once you know that is working then use the technique you have already used in the "What I have tried" section of your question - generate the SQL dynamically based on the actual data content of the table instead of the hard-coded column names - here is a snippet of that (I'll leave the rest to you)

SQL

select @cols = STUFF((SELECT ',' + QUOTENAME(ItemName + 'qty') +  ',' + QUOTENAME(ItemName + 'price') 
    from yourtable
    group by ItemName
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

If you want it displayed precisely as you have in your expected results then you should do that in your presentation layer, not the query itself