Mahendra_P
Starting Member
1 Post |
Posted - 2012-10-16 : 15:36:21
|
--Hi,--Below are the tables i have created Agent, Contact and MTOPTIONs --and data also be inserted into those tables:CREATE TABLE AGENT( AGENTID INT IDENTITY(1,1) PRIMARY KEY ,NAME VARCHAR(20));GOCREATE TABLE CONTACT(CONTACTID INT IDENTITY(1,1) PRIMARY KEY ,AGENTID INT FOREIGN KEY REFERENCES AGENT(AGENTID),CONTACTTYPEID INT,CONTACTINFO VARCHAR(20));GOCREATE TABLE MTOPTION(OPTIONID INT IDENTITY(1,1) PRIMARY KEY,CATEGORY VARCHAR(20),NAME VARCHAR(20));GOINSERT INTO AGENT(NAME) VALUES ('MAHI'),('SURYA'),('NAVEEN'),('CHAKRI')INSERT INTO MTOPTION (CATEGORY,NAME) VALUES ('BUSINESS','BPHONE'),('BUSINESS','BEMAIL') ,('HOME','HPHONE'),('HPME','HEMAIL')GO INSERT INTO CONTACT VALUES (1,1,'123456789') ,(1,2,'MAHI@GMAIL.COM') ,(2,1,'456456456') ,(2,2,'SURYA@GMAIL.COM') ,(3,1,'123123123') ,(3,2,'NAVEEN@GMAIL.COM')Go--Below one is the query to get data from above three tablesSELECT A.NAME,MT.Name,C.CONTACTINFO FROM CONTACT C INNER JOIN AGENT A ON A.AGENTID=C.AGENTID INNER JOIN MTOPTION MT ON MT.OPTIONID=C.CONTACTTYPEIDOut Put is as shown below:AName(col1) CTType(Col2) CTInfo (Col3)MAHI BPHONE 123456789MAHI BEMAIL MAHI@GMAIL.COMSURYA BPHONE 456456456SURYA BEMAIL SURYA@GMAIL.COMNAVEEN BPHONE 123123123NAVEEN BEMAIL NAVEEN@GMAIL.COMI want to display the above data in below format(We have two types of contact informations 1. Phone and 2. Email)AName(col1) CTType(Either PHONE or EMAIL)(col2) CTINFO(Phone)(col3) CTINFO(Email(col4)Mahi BPHONE/BEMAIL 123456789 mahi@gmail.comsury BPHONE/BEMAIL 456456456 surya@gmail.comnaveen BPHONE/BEMAIL 123123123 naveen@gmail.com Can you please help me to display data in above format.Thanks & RegardsMahendraP(8008412791) |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-16 : 15:56:04
|
If you have only those two types then you can do it like shown below. But, if you have many types and the number of types are unknown, you will need to use dynamic pivoting. Take a look at Madhivanan's blog for a function and examples of how to do this: http://beyondrelational.com/modules/2/blogs/70/posts/10791/dynamic-crosstab-with-multiple-pivot-columns.aspxSELECT A.NAME, 'BPHONE/BEMAIL' AS CTType, MAX(CASE WHEN MT.Name = 'BPHONE' THEN C.CONTACTINFO END) AS CTINFOPHone, MAX(CASE WHEN MT.Name = 'BEMAIL' THEN C.CONTACTINFO END) AS CTINFOPHoneFROM CONTACT CINNER JOIN AGENT A ON A.AGENTID=C.AGENTID INNER JOIN MTOPTION MT ON MT.OPTIONID=C.CONTACTTYPEIDGROUP BY A.NAME |
|
|