How to spread columns into rows in SQL

Question

0.00/5 (No votes)

See more:

I have tables like this

Table1

Cid	FlagId	AdjusterId
1	1	1
2	2	2
3	1	1
4	3	2
5	4	1
6	2	1

Table2

FlagId	FlagName
1	F1        
2	F2        
3	F3        
4	F4       

Table3

AId	AName
1	ABC       
2	XYZ

I need output as below

AName	F1	F2   F3   F4
ABC	2	1    0    1
XYZ	0	1    1    0

What I have tried:

Is it achievable with pivot.

SQL

WITH PIVOTED
AS (
	SELECT *
	FROM Claim c
	inner join Flag f on f.flagId = c.FlagId
		PIVOT(Sum(f.FlagId)  
			FOR f.FlagName IN([F1], [F2],[F3],[F4]) 
		) P
)
SELECT 
	*
FROM PIVOTED

I am not getting result as expected

Posted 6-Mar-18 6:40am

Member 8478234

Updated 20-Mar-18 19:25pm

Chris Maunder772.9K

v3

Add a Solution

3 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)

Maciej Los · Answer 1 · 2018-03-06T09:46:00

Check this:

SQL

SELECT AName, [F1], [F2], [F3], [F4]
FROM (
	SELECT t2.FlagName, t3.AName 
	FROM @Table1 AS t1
		INNER JOIN @Table2 AS t2 ON t1.FlagId = t2.FlagId  
		INNER JOIN @Table3 AS t3 ON t1.AdjusterId = t3.AId 
	) AS DT 
PIVOT(COUNT(FlagName) FOR FlagName IN ([F1], [F2], [F3], [F4])) AS PVT

Result:

AName   F1      F2      F3     F4
ABC     2	    1	    0	    1
XYZ	    0	    1	    1	    0

Note: Replace @Table1, @Table2, @Table3 with proper table name.

For further details, please see:
Using PIVOT and UNPIVOT | Microsoft Docs[^]
Visual Representation of SQL Joins[^]

Tisham Ahuja · Answer 2 · 2018-03-06T09:12:00

I have not used PIVOT, however it can be done using derived, temp table or table variable etc.. Below is using derived table:

SELECT AId, SUM(Derived.F1) AS F1,SUM(Derived.F2) AS F2,SUM(Derived.F3) AS F3 ,SUM(Derived.F4) AS F4 FROM 
(
SELECT T3.AID,
CASE FName WHEN 'F1' THEN COUNT(1)  ELSE 0 END AS F1,
CASE FName WHEN 'F2' THEN COUNT(1)  ELSE 0 END AS F2,
CASE FName WHEN 'F3' THEN COUNT(1)  ELSE 0 END AS F3,
CASE FName WHEN 'F4' THEN COUNT(1)  ELSE 0 END AS F4
 FROM TABLE2 T2 INNER JOIN TABLE1 T1 ON T2.FId = T1.FId INNER JOIN TABLE3 T3 ON T1.AId = T3.AId
GROUP BY T3.AID, FName
) Derived GROUP BY AID

Hope this helps.. Enjoy coding :)

Member 13737936 · Answer 3 · 2018-03-20T19:26:00

Solution 3

how i create a student attendence list in sql out attendence give win student touch the thumb impression;

Posted 20-Mar-18 19:26pm

Member 13737936

Comments

Maciej Los 21-Mar-18 3:09am

This is not an answer. Please, delete it. If you want to post a question, please ASK

How to spread columns into rows in SQL

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0