How to PIVOT data according to type in SQL server

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

This is my data 

ACTUAL	BUDGET	TYPE
90	100	A
125	130	A		
100	110	B
100	222	B
150	150	B
 And i want like this 

A_BUDG	B_BUDG A_ACT B_ACT
100	110	90	100
130	222	125	100
	150	        150

here A_BUDG FOR A BUDGET AND A_ACT FOR A ACTUAL
Please give me suggestion.

What I have tried:

this is my sql qry for first one

SELECT R.* FROM
(
select D.ACTUAL,D.BUDGET,'A' AS TYPE from  RENT_T_PARKING_HDR H
left join RENT_T_PARKING_DTL D ON H.VOUCHERNO=D.VOUCHERNO
WHERE TIMEFROM BETWEEN 3 AND 4 AND TIMETO BETWEEN  3 AND 4

UNION

select D.ACTUAL,D.BUDGET,'B' AS TYPE from  RENT_T_PARKING_HDR H
left join RENT_T_PARKING_DTL D ON H.VOUCHERNO=D.VOUCHERNO
WHERE TIMEFROM BETWEEN 1 AND 2 AND TIMETO BETWEEN  1 AND 2

)R GROUP BY R.TYPE,R.ACTUAL, R.BUDGET ORDER BY TYPE

and need help for desired output

Posted 18-Dec-18 20:47pm

SujataJK

Updated 19-Dec-18 1:41am

v3

Add a Solution

Comments

Herman<T>.Instance 19-Dec-18 4:49am

There are many solutions to be found i the Article sections of CP:

Like mine: https://www.codeproject.com/Articles/201320/Dynamic-Pivoting-with-Cubes-and-eventhandlers-in-S

1 solution

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)

MadMyche · Answer 1 · 2018-12-19T01:42:00

SQL server has a PIVOT function which "flattens" out rows of data into columns. The syntax for the statement has at least one unpivoted columns and then multiple columns which are pivoted based on that (unpivoted column).

@digimanus has an article on how to create a Dynamic Pivot which utilizes a data CUBE for more advanced queries which may be needed with some datasets.

For your particular question, I cannot determine what the pivot point is; so I cannot provide a code-answer. The "data" you have provided seems to be incomplete; you are showing one data table but the queries you have are referencing two separate tables.

References
MSDN: Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs[^]

Digimanus article: Dynamic Pivoting with Cubes and eventhandlers in SQL Server 2005, 2008 and 2008 R2[^]