Attribute SQL Query - one Attribute per column

Hi Lily,

I'd be willing to bet they built it as a Matrix report with a column group on keyword_no.

Hello Lily,

I agree with Sara, it looks like the query can return multiple lines per customer which would mean that the report is probably built around a column group.

What you could do if you are trying to do a query is use a dynamic pivot, a normal pivot if you know how many columns you will need in total, or, if you know exactly what specific attributes you will be needing for this query (and it looks like you might), you could basically just use nested queries as tables and then just group and coalesce the null column values into a single row per customer.

John

try this

select
customer_no,
max(sort_name) as sort_name,
max(fname) as fname,
max(lname) as lname,
max(last_gift_dt) as last_gift_dt,
max(last_ticket_dt) as last_ticket_dt,
max(Attribute_1) as Attribute_1,
max(Attribute_2) as Attribute_2,
max(Attribute_3) as Attribute_3

from
(
SELECT DISTINCT k.customer_no
, c.sort_name
, c.fname
, c.lname
, c.last_gift_dt
, c.last_ticket_dt
, case when k.keyword_no = 532 then k.key_value else '' end AS 'Attribute_1'
, case when k.keyword_no = 533 then k.key_value else '' end AS 'Attribute_2'
, case when k.keyword_no = 534 then k.key_value else '' end AS 'Attribute_3'
FROM T_CUSTOMER c
JOIN T_LIST_CONTENTS l on l.customer_no = c.customer_no
JOIN TX_CUST_KEYWORD k on k.customer_no = c.customer_no
WHERE l.list_no = 8340 and k.keyword_no IN (532,533,534)
) a
group by customer_no
ORDER BY customer_no

Mendy - THANK YOU! That's perfect! I appreciate it so much!

you're welcome.

If you want to do this for any number of keywords, you can use the pivot function available inside SQL.  Here's your code (sans the list_no value, which I don't have and filled in with some of our own keyword numbers):

USE [impresario];
GO

DECLARE @cols NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT DISTINCT TOP 100 PERCENT '],[' + CONVERT(VARCHAR(30), k.keyword_no)
FROM T_CUSTOMER c
JOIN T_LIST_CONTENTS l
ON l.customer_no = c.customer_no
JOIN TX_CUST_KEYWORD k
ON k.customer_no = c.customer_no
--WHERE l.list_no = 8340
WHERE k.keyword_no IN (523, 514, 520)
ORDER BY '],[' + CONVERT(VARCHAR(30), k.keyword_no)
FOR
XML PATH('')), 1, 2, '') + ']'

--SELECT 'debug', @cols

DECLARE @nsql NVARCHAR(4000)

SELECT DISTINCT
k.customer_no,
c.sort_name,
c.fname,
c.lname,
c.last_gift_dt,
c.last_ticket_dt,
k.keyword_no,
k.key_value
INTO #Pivot
FROM T_CUSTOMER c
JOIN T_LIST_CONTENTS l
ON l.customer_no = c.customer_no
JOIN TX_CUST_KEYWORD k
ON k.customer_no = c.customer_no
--WHERE l.list_no = 8340
WHERE k.keyword_no IN ( 523, 514, 520 );

--SELECT 'debug', * FROM #Pivot

SET @nsql = N'SELECT DISTINCT
customer_no,
sort_name,
fname,
lname,
last_gift_dt,
last_ticket_dt, ' + @cols + ' FROM
(
SELECT DISTINCT
customer_no,
sort_name,
fname,
lname,
last_gift_dt,
last_ticket_dt,
keyword_no,
key_value
FROM #Pivot
) AS p
PIVOT
(
MIN([key_value])
FOR keyword_no IN
( ' + @cols + ' )
) AS pvt ORDER BY customer_no'

--SELECT 'debug', @nsql

EXEC sp_executesql @nsql

DROP TABLE #Pivot

-- end

Comment out the 'WHERE l.list_no' clause and change the k.keyword_no IN ( 523, 514, 520 ) (2 places) to your numbers.  When you execute this, they keyword numbers become columns and the key values become the values under each one.  You can use this for any number of keywords that you want to pivot in the future.  I left some 'debug' statements in there (commented out though) in case you want to see the data along the way.

HTH

this is perfect. 

School is in session - I think my brain just grew
Got to love this community.  This is exactly what I was working on yesterday with very limited success.

Thanks folks