I want to pivot data of SQL server (i.e column as rows and rows as column)

Question

1.00/5 (1 vote)

See more:

I have Data as follows

CompanyName  Year   Sales  TradedSales  TotalSales	Taxes	NetSales  RowMaterial
       TCS	2017      201	   1245	       65650	  23	  3232     323
       TCS	2018      122	   121	       2121	  121	  21231	   2121
    Tata Moters 2015      14	    54	        54	   545	   456	   4548
   Tata Moters	2017      2121	    12	        21	   156	   565	   5656	
   Tata Moters	2018      7512	   5454	        545	   5454    787	    78
     SMERA	2017      5544	   2121	        545	   9522	    878	    121
     SMERA	2017      784	    787	        998	    854	    545	     545

I want Output as

CompanyName    TCS   TCS   [Tata Moters]  [Tata Moters]  [Tata Moters]  SMERA   SMERA
Year           2017   2018     2015          2017           2018         2017    2017    
Sales           201   122      14            2121           7512         5544    784
TradedSales    1245   121      54             12            5454         2121    784
TotalSales     65650  21       21             54            21           545     998
Taxes           23    21       545            156           5454          9522    854
NetSales        3232  21231    456            565	    787           878    121
RowMaterial     323   2121     4548           5656	    78            121    545


also there are many companies data for many years so Want solution dynamic

What I have tried:

I dont have Idea About Pivot so Please  provide solution for problem
thanks in advance

Posted 15-Feb-18 9:27am

paul_vin

Updated 15-Feb-18 10:14am

OriginalGriff

Add a Solution

2 solutions

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)

OriginalGriff · Answer 1 · 2018-02-15T09:36:00

That isn't how it works: we are not a "code to order" service.
If you want work done for you - and from your previous questions that is exactly what you do want - then you will have to pay. I suggest you go to freelancer.com and ask there.

Alternatively, read this: Using PIVOT and UNPIVOT[^] and start learning, because at the moment, you are coming over as a Help Vampire and that is not good.

Jörgen Andersson · Answer 2 · 2018-02-15T10:14:00

Solution 2

The worst part is that you haven't even bothered to search. You have all the necessary search words in your question.
The very first result here[^] is what you need

Posted 15-Feb-18 10:14am

Jörgen Andersson

Comments

paul_vin 16-Feb-18 1:56am

I have written code but it is not giving Output as required above

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(CompanyName)
from tblCompanyData
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = 'SELECT Year,' + @cols + '
from
(
select [CompanyName]
,[Year]
,[Sales]
,[TradedSales]
,[TotalSales]
,[Taxes]
,[NetSales]
,[RowMaterial]
,[Power]
,[Consumables]
,[Salaries]
,[ManufacturingCost]
,[OtherExpenses]
,[TotalCost]
,[OperatingProfit]
,[Depreciation]
,[Interest]
,[Profitpostinterest]
,[IncomeTax]
,[NetProfit]
,[ID]
FROM tblCompanyData
) x
pivot
(
max(ID)
for CompanyName in (' + @cols + ')
) p '

execute(@query)