TABLE PIVOT INEER JOIN IN SQL

Question

1.00/5 (2 votes)

See more:

Hi Friends

~~It's urgent~~

My problem is...
I have two table
First table naem is P

===============================
id	PARENTID	ITEM                 
1	NULL	     EMPLOYE  PERSON         
2	1	     UNEMPLOYPERSON          
3	1	     MALEUNEMPLOY            
4       1 	     FEMALEUNEMPLOY          
5	NULL	     EMPLOYE  PERSON         
6	1	     UNEMPLOYPERSON          
7	1	     MALEUNEMPLOY            
8       1 	     FEMALEUNEMPLOY          


================================

==================================

Second table is PV

=====================================
id	PID	VALUE
1	1	20
2	2	45
3	3	5
4	4	12
=================================

where PID is P Table ID

And the last table is TP

======================================
ID    PID           NAME           PrentID
1     1             COUNTRY         null
2     1             STATE            1
3     1             CITY             2
4     2             COUNTRY          null
5     2             STATE            1
6     2             CITY             2
==================================

where pid is P Table ID

How can I display this like ...

IN CITY
TOTALPERSON     ENEMPLOYPERSON       EMPLOYPERSON    MALEUNEMPLOY     
82                45                      20                 5        


IN STATE
TOTALPERSON     ENEMPLOYPERSON       EMPLOYPERSON    MALEUNEMPLOY     
82                45                      20                 5

This is not static it is dynamic depending on area selected like ...COUNTRY,STATE,CITY
[Edit - CHill60 SHOUTING removed]

Posted 20-May-13 1:31am

rahul 92

Updated 23-May-13 2:54am

Maciej Los

v6

Add a Solution

Comments

[no name] 20-May-13 7:38am

No it's not urgent at all. Are you screaming because you think it's urgent?

rahul 92 23-May-13 8:38am

HII.....
I Have Update My Qestion Plzzzzz Give Me Some Solution...I'm Now Online

rahul 92 23-May-13 8:52am

It's Urgent

Aarti Meswania 20-May-13 7:39am

where is relation of city & state with employee?

rahul 92 23-May-13 8:37am

HII.....
I Have Update My Qestion
Plzzzzz Give Me Some Solution...I'm Now Online

rahul 92 24-May-13 1:27am

Thanks .........
For Solution

Aarti Meswania 24-May-13 1:57am

Welcome!
Glad to help you! :)

rahul 92 24-May-13 3:19am

But My problem is not Solved..

Aarti Meswania 24-May-13 3:22am

till now what result you get just paste your table result here

rahul 92 24-May-13 4:22am

I want Result like..
To According My Question

TOTALPERSON ENEMPLOYPERSON EMPLOYPERSON MALEUNEMPLOY
82 45 20 5

Specification ...
TotalEmploye is total of all employ..
it similar for ENEMPLOYPERSON, EMPLOYPERSON,MALEUNEMPLOY

ENEMPLOYPERSON, EMPLOYPERSON,MALEUNEMPLOY this are rows in my table
but i need their sum of value in a colum..
theair value are in another table tahat table have only id of them item
which is shown in my question ....

Aarti Meswania 24-May-13 4:27am

have you try my updated solution "Solution 1"?

rahul 92 24-May-13 4:59am

in ur update qury gives me error.this is.
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

some solution

Aarti Meswania 24-May-13 5:54am

table structre is wrong so I could not help you more try this...

with a as
(
select 'Total Employees' as item, sum(pv.value) as [value]
from p
inner join pv on p.id=pv.pid

union all

select p.item,pv.value
from p
inner join pv on p.id=pv.pid
)
SELECT [Total Employees],[EMPLOYE PERSON],[UNEMPLOYPERSON],[MALEUNEMPLOY]
FROM
a
PIVOT
(Sum([value]) FOR [item]
IN ( [Total Employees],[EMPLOYE PERSON],[UNEMPLOYPERSON],[MALEUNEMPLOY])
) AS b

rahul 92 24-May-13 3:23am

becuz it is not pivot the table,
i need my row as acolumn !!!
may u give me any web link in which more than one table are joining which has sum any coumn value and pivot..
plz ...
i have submit this today to my boss

Corporal Agarn 20-May-13 9:05am

What have you tried so far?

rahul 92 23-May-13 8:38am

HII.....
I Have Update My Qestion Plzzzzz Give Me Some Solution...I'm Now Online

Maciej Los 20-May-13 16:57pm

Your problem is that your question is not clear. Sorry, but you nned to update your question using "Improve question" widget.
You need to provide the relationships between tables.

rahul 92 23-May-13 8:38am

HII.....
I Have Update My Qestion Plzzzzz Give Me Some Solution...I'm Now Online

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)

Aarti Meswania · Answer 1 · 2013-05-23T03:31:00

SQL

with a as
(
select 'Total Employees' as item, sum(pv.value) as [value]
from p
inner join pv on p.id=pv.pid

union all
 
select p.item,pv.value
from p
inner join pv on p.id=pv.pid
)
SELECT [Total Employees],[EMPLOYE  PERSON],[UNEMPLOYPERSON],[MALEUNEMPLOY]
FROM
a
PIVOT
(Sum([value]) FOR [item]
    IN ( [Total Employees],[EMPLOYE  PERSON],[UNEMPLOYPERSON],[MALEUNEMPLOY])
) AS b

Happy Coding!
:)

Maciej Los · Answer 2 · 2013-05-23T06:50:00

Try dynamic pivot:

SQL

DECLARE @cols NVARCHAR(300)
DECLARE @dt NVARCHAR(2000)
DECLARE @pt NVARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT '],[' + [ITEM]
			FROM P
			ORDER BY '],[' + [ITEM]
		FOR XML PATH('')),1,2,'') + ']'

SET @dt = N'SELECT TP.[NAME], PV.[VALUE], P.ITEM ' + 
	'FROM P INNER JOIN TP ON P.ID = TP.PID ' +
		'INNER JOIN PV ON P.ID = PV.PID '

SET @pt = 'SELECT [NAME], ' + @cols + ' ' +
         'FROM (' + @dt + ') AS DT ' +
         'PIVOT(SUM([VALUE]) FOR [ITEM] IN(' + @cols + ')) AS PT'

EXEC (@pt)

TABLE PIVOT INEER JOIN IN SQL

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us