How to split a column's data into different with same Header in PIVOT?

Question

I have used the following query to extract the member data with their interest using PIVOT.

CREATE Table    #MEMBER_ATTRIBUTES   (
MEMBER_ID int,
MEMBER_PROPERTY varchar( 500 ),
MEMBER_VALUE varchar( 500 ),
PRMEDIAUSER_ID int,
MEMBER_FULL_NAME nvarchar( 500),
MEMBER_LAST_NAME  nvarchar(500 ),
MEMBER_MID_INIT nvarchar( 500),
MEMBER_FIRST_NAME nvarchar( 500),
MEMBER_EMAIL nvarchar( 500),
MEMBER_FAX nvarchar( 500),
MEMBER_SEND_BY_FAX nvarchar(500 ),
MEMBER_LANGUAGE nvarchar( 500),
COUNTRY_ID int,
MEMBER_ACTIVE int,
MEMBER_DATE_ADDED datetime,
MEMBER_COMPANY  nvarchar(500 ),
MEMBER_ADDRESS nvarchar( 500),
MEMBER_CITY nvarchar( 500),
MEMBER_STATE nvarchar( 500),
MEMBER_ZIP nvarchar( 500),
MEMBER_POSTALCODE nvarchar( 500),
MEMBER_PHONE nvarchar( 500),
MEMBER_NEAREST_AIRPORT nvarchar(500 ),
MEMBER_OCCUPATION nvarchar( 500),
MEMBER_COUNTY nvarchar( 500)
)
insert INTO #MEMBER_ATTRIBUTES ( MEMBER_EMAIL,MEMBER_ID ,MEMBER_PROPERTY
, MEMBER_VALUE,PRMEDIAUSER_ID, MEMBER_FULL_NAME,MEMBER_LAST_NAME ,MEMBER_MID_INIT, MEMBER_FIRST_NAME,
MEMBER_FAX,MEMBER_SEND_BY_FAX ,MEMBER_LANGUAGE , COUNTRY_ID ,MEMBER_ACTIVE , MEMBER_DATE_ADDED ,
MEMBER_COMPANY  ,MEMBER_ADDRESS , MEMBER_CITY ,MEMBER_STATE ,MEMBER_ZIP ,MEMBER_POSTALCODE ,
MEMBER_PHONE ,MEMBER_NEAREST_AIRPORT ,MEMBER_OCCUPATION , MEMBER_COUNTY
) select distinct( m.MEMBER_EMAIL ),isnull( m.MEMBER_ID ,'0' ),
ISNULL ( [gmin].[MEMBER_PROPERTY] , '''' ) AS MEMBER_PROPERTY
, ISNULL ( [gmin]. [MEMBER_VALUE] ,'''' ) AS MEMBER_VALUE,
m.PRMEDIAUSER_ID ,m.MEMBER_FULL_NAME ,m.MEMBER_LAST_NAME ,m.MEMBER_MID_INIT ,
m.MEMBER_FIRST_NAME ,m.MEMBER_FAX ,m.MEMBER_SEND_BY_FAX ,
m.MEMBER_LANGUAGE ,m.COUNTRY_ID ,m.MEMBER_ACTIVE ,m.MEMBER_DATE_ADDED ,
m.MEMBER_COMPANY ,m.MEMBER_ADDRESS ,m.MEMBER_CITY ,m.MEMBER_STATE ,
m.MEMBER_ZIP ,m.MEMBER_POSTALCODE ,m.MEMBER_PHONE ,m.MEMBER_NEAREST_AIRPORT ,
m.MEMBER_OCCUPATION ,m.MEMBER_COUNTY
from SUBSCRIBER s
inner join V3_listserv.dbo .MEMBER m on m.MEMBER_EMAIL = s.SUBSCRIBER_EMAIL
left join V3_listserv.dbo .MEMBER_ATTRIBUTES gmin on gmin.MEMBER_ID= m.MEMBER_ID
where s .SUBSCRIBER_GROUP  ='D2013'
and m .MEMBER_ACTIVE= 1
--select * from #MEMBER_ATTRIBUTES
DECLARE @cols AS NVARCHAR( MAX ),
    @query  AS NVARCHAR ( MAX )
SELECT @cols= stuff((
    SELECT distinct ', ' +QUOTENAME ( MAX ( MEMBER_PROPERTY ))
    FROM #MEMBER_ATTRIBUTES
    group by MEMBER_VALUE
    --order by MEMBER_VALUE
    FOR XML PATH( '' )), 1 , 2, '');
SET @query = 'SELECT MEMBER_ID,PRMEDIAUSER_ID,
MEMBER_FULL_NAME,MEMBER_LAST_NAME,MEMBER_MID_INIT ,MEMBER_FIRST_NAME,
MEMBER_EMAIL,MEMBER_FAX ,MEMBER_SEND_BY_FAX,MEMBER_LANGUAGE ,COUNTRY_ID ,
MEMBER_ACTIVE,MEMBER_DATE_ADDED,MEMBER_COMPANY ,MEMBER_ADDRESS ,MEMBER_CITY,
MEMBER_STATE ,MEMBER_ZIP ,MEMBER_POSTALCODE,MEMBER_PHONE ,MEMBER_NEAREST_AIRPORT ,
MEMBER_OCCUPATION,MEMBER_COUNTY  ' + @cols + '
from
 (
 SELECT MEMBER_ID,MEMBER_VALUE,MEMBER_PROPERTY,PRMEDIAUSER_ID,MEMBER_FULL_NAME,
MEMBER_LAST_NAME,MEMBER_MID_INIT ,MEMBER_FIRST_NAME,MEMBER_EMAIL,MEMBER_FAX ,
MEMBER_SEND_BY_FAX,MEMBER_LANGUAGE ,COUNTRY_ID ,MEMBER_ACTIVE,MEMBER_DATE_ADDED,
MEMBER_COMPANY ,MEMBER_ADDRESS ,MEMBER_CITY,MEMBER_STATE ,MEMBER_ZIP ,
MEMBER_POSTALCODE,MEMBER_PHONE ,MEMBER_NEAREST_AIRPORT ,MEMBER_OCCUPATION,
MEMBER_COUNTY  FROM #MEMBER_ATTRIBUTES
 ) x
pivot
 (
   MAX(MEMBER_VALUE)
for x.MEMBER_PROPERTY in (' + @cols + ')
    ) p'
execute sp_executesql @query;
drop table #MEMBER_ATTRIBUTES

It results me in the following format

Result of above query

Now you can see in the output for "My main interests" is "Gastronomy/Culinary, History/local culture"

I want output for "My Main Interest" Column

My main Intreset         My main Intreset
Gastronomy/Culinary      History/local culture

there can be more value separated by ","(comma). i want a new column for each comma separated value as above and below.

My main Intreset         My main Intreset          My main Intreset
Gastronomy/Culinary      History/local culture     Culture

I am not sure how i can i achieve this. is there any way to achieve this output. please help me i am stuck here. any help will be appreciated. thanks

You cannot have two or more fields with the same name – Serpiton May 19 '14 at 07:42 — Serpiton, May 19 '14 at 07:42
We can add 1,2,3 ..... with column names... – Ram Singh May 19 '14 at 07:45 — Ram Singh, May 19 '14 at 07:45

score 1 · Answer 1 · edited May 19 '14 at 05:29

Hi i have not read out your query but i am helping you by giving one example

this is the string i have

questintitle column name how to check visitor no, of web site in asp.net ?

Now if i want to split by , (comma) so my query will looks like

select top(1)  Substring(questiontitle,1,Charindex(',', questiontitle)-1) as 
Name,Substring(questiontitle, Charindex(',', questiontitle)+1, 
LEN(questiontitle)) as Surname from QuestionMaster

This will result into

Name >>how to check visitor no
Surname >>of web site in asp.net ?

I hope this will help you to accomplish your requirement :)

How to split a column's data into different with same Header in PIVOT?

1 Answers1