How to Use PIVOT with tables joined from two databases in SQL Server 2008 R2

Question

I am getting data after joined two databases, first is "listserver" and second is "newsletter".

Here is my original query which returns 4 records for one subscriber.

select 
    gmin. MEMBER_PROPERTY,gmin .MEMBER_VALUE , m.*  
from 
    SUBSCRIBER s
inner join 
    listserv.dbo .MEMBER m on m.MEMBER_EMAIL= s.SUBSCRIBER_EMAIL
inner join 
    listserv.dbo .MEMBER_ATTRIBUTES gmin on gmin.MEMBER_ID= m.MEMBER_ID
where 
    s .SUBSCRIBER_GROUP  like '%abc%' 
order by 
    m.MEMBER_DATE_ADDED desc

This query returns 4 rows for a single subscribers as I have 4 entries in "MEMBER_ATTRIBUTES" table.

Here is the structure

enter image description here

Now I want to get this data in one row for each subscriber. I know I can do it using PIVOT, but i don't know how to use PIVOT with above query.

Please help me how can i achieve my goal, thanks in advance.

UPDATE I want result in below format:

I want result in this format

I'm maybe not sure it's PIVOT you're after - could you give an example of how the 'result' should be? — Allan S. Hansen, Apr 16 '14 at 05:22
@AllanS.Hansen please check i have updated the question with the required output screenshot — Ram Singh, Apr 16 '14 at 05:26
See this link http://stackoverflow.com/a/11122225/2806972 may be its help you. — Kumar Shanmugam, Apr 16 '14 at 06:07
You need dynamic pivot here. Pls provide some sample data (in script form, not as a screenshot!) to get a query you need. I could also give you a generalized sample you could work from. — dean, Apr 17 '14 at 09:04

score 0 · Answer 1 · edited May 23 '17 at 11:57

I have used the below query to achieve my goal. Use PIVOT for Multiple records in SQL SERVER and Getting Null data while using PIVOT in sql server 2008 R2 questions has helped me to build this query.

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_ID ,MEMBER_PROPERTY
, MEMBER_VALUE,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 
) select 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_EMAIL,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 listserv.dbo .MEMBER m on m.MEMBER_EMAIL= s.SUBSCRIBER_EMAIL
left join listserv.dbo .MEMBER_ATTRIBUTES gmin on gmin.MEMBER_ID= m.MEMBER_ID
 where s .SUBSCRIBER_GROUP  like '%abc%' 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

How to Use PIVOT with tables joined from two databases in SQL Server 2008 R2

1 Answers1