Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to use pivot in this scenario????

Author  Topic 

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-18 : 10:54:57
I have an requirement where i need to show Employee Table and CustomerMeta Table joins In CustomerMeta Table (CustID)
Reference to Employee Table and Metavalues table Metavalues table is like master table.
In Application i will get multiple select box selection (DrivingLicense,Passport etc;) so that data will be inserted
in comma(',') seperated values So in my desired output i need to show as i need to show comma seperated values
As columns and they have inserted in CustomerMeta Table (DrivingLicense,Passport) they need to show as YES OR NO as
i shown in output....Suggest me


Metavalues table :

MetaID Metavaluedescription
1 Driving License
2 Passport
3 AadharCard
4 EducationalProof
5 ResidentialProof


CustomerMeta Table :


CustID MetaTypeID MetaTypeName
2 1,2,3,4,5 DrivingLicense,Passport,AadharCard,EducationalProof,ResidentialProof
3 1,2,3 DrivingLicense,Passport,AadharCard


Employee Table


EmpID CustID EmPname
1001 2 Mohan
1002 3 ramu


Desired OutPut :


EmpID CustID EmPname DrivingLicense Passport AadharCard EducationalProof ResidentialProof
1001 2 Mohan YES NO YES NO NO
1002 3 ramu NO YES NO NO NO


P.V.P.MOhan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-18 : 13:59:43
[code]
create table Metavalues
(
MetaID int,
Metavaluedescription varchar(100)
)
insert Metavalues
values(1, 'DrivingLicense'),
(2 ,'Passport'),
(3 ,'AadharCard'),
(4 ,'EducationalProof') ,
(5 ,'ResidentialProof')


create table CustomerMeta
(
CustID int,
MetaTypeID varchar(100),
MetaTypeName varchar(1000)
)
insert CustomerMeta
values(2, '1,2,3,4,5', 'DrivingLicense,Passport,AadharCard,EducationalProof,ResidentialProof'),
(3, '1,2,3', 'DrivingLicense,Passport,AadharCard')


create table Employee1
(
EmpID int,
CustID int,
EmPname varchar(100)
)
insert Employee1
values(1001, 2, 'Mohan'),
(1002, 3 ,'ramu')

DECLARE @MetavaluesList varchar(max)
SELECT @MetavaluesList= STUFF((SELECT ',[' + Metavaluedescription + ']' FROM Metavalues ORDER BY MetaID FOR XML PATH('')),1,1,'')

DECLARE @SqlQuery varchar(max)
SET @SqlQuery='
SELECT *
FROM
(
SELECT e.*,Metavaluedescription
FROM CustomerMeta m
JOIN Employee1 e
ON e.CustID = m.CustID
JOIN Metavalues v
ON '','' + m.MetaTypeName + '','' LIKE ''%,'' + v.Metavaluedescription + '',%''
)m
PIVOT (COUNT(Metavaluedescription) FOR Metavaluedescription IN (' + @MetavaluesList + '))p'

EXEC(@sqlquery)

DROP TABLE CustomerMeta
DROP TABLE Employee1
DROP TABLE Metavalues


output
---------------------------------------------------------------------------------------------------------------------
EmpID CustID EmPname DrivingLicense Passport AadharCard EducationalProof ResidentialProof
---------------------------------------------------------------------------------------------------------------------
1001 2 Mohan 1 1 1 1 1
1002 3 ramu 1 1 1 0 0

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-19 : 02:00:34
Do you want YES/NO based on count?

SELECT EmpID, CustID,
CASE WHEN [DrivingLicense] >0 THEN 'YES' ELSE 'NO' END [DrivingLicense],
CASE WHEN [Passport] >0 THEN 'YES' ELSE 'NO' END [Passport],
CASE WHEN [AadharCard] >0 THEN 'YES' ELSE 'NO' END [AadharCard],
CASE WHEN [EducationalProof] >0 THEN 'YES' ELSE 'NO' END [EducationalProof],
CASE WHEN [ResidentialProof] >0 THEN 'YES' ELSE 'NO' END [ResidentialProof]

FROM
(
SELECT e.*,Metavaluedescription
FROM CustomerMeta m
JOIN Employee1 e
ON e.CustID = m.CustID
JOIN Metavalues v
ON ',' + m.MetaTypeName + ',' LIKE '%,' + v.Metavaluedescription + ',%'
)m
PIVOT (COUNT(Metavaluedescription) FOR Metavaluedescription IN ([DrivingLicense],[Passport],[AadharCard],[EducationalProof],[ResidentialProof]))p


--
Chandu
Go to Top of Page

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-19 : 02:06:43
thanks visakh and chandu but please suggest me the recent posted query

P.V.P.MOhan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-19 : 02:16:22
whats the recent posted query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-19 : 02:18:16
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=184687

P.V.P.MOhan
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-19 : 02:28:43
quote:
Originally posted by mohan123

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=184687
P.V.P.MOhan



Check reply in the above link

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-19 : 02:33:39
Why are you opening multiple threads for same question? why not keep every related posts in same thread?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -