• Replies 8 replies
  • Subscribers 335 subscribers
  • Views 232 views
  • Users 0 members are here
Related

Attribute SQL Query - one Attribute per column

Former Member
Former Member $organization

I'm trying to write a query in SSMS that can output a selection of Attributes as individual columns, with the keyword value for each attribute as the row value. I found code for a procedure in Shared reports (LP_BSO_LIST_ATTRIBUTES_NEW) that seems to be doing this based on the example output, but I don't quite understand how!

I'm currently using this, but it returns duplicate lines, with one row per Attribute column. Any thoughts on how to write this so that I'm only returning one row per constituent, with the Attributes all returned in the same row?

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 IN (533, 532) then k.key_value else NULL end AS 'Attribute 1'
 , case when k.keyword_no = 534 then k.key_value else NULL end AS 'Attribute 2'
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)
ORDER BY customer_no


The store procedure from BSO has a selection statement that looks like this:
SELECT	cus.customer_no,
	[dbo].FS_CONST_STRING_NEW(cus.customer_no, 'Y') as const,
	kwd.description	as attribute,
	case when kwd.data_type = @date_data_typ
		then CAST(CAST(cky.key_value as DATE) as varchar)
		else cky.key_value 
		end	as att_value,
	cus.sort_name				
	FROM T_CUSTOMER	cus  
	left join TR_NAMESTATUS	sts on cus.name_status	= sts.id
		join TX_CUST_KEYWORD	cky  on cky.customer_no = cus.customer_no
		join T_KEYWORD		kwd  on kwd.keyword_no	= cky.keyword_no
		join TR_CUST_TYPE	cty  on cus.cust_type	= cty.id	
		join dbo.FT_SPLIT_LIST(@attribute_list,',') par on kwd.keyword_no = par.element	


Any insight on how that is returning one key_value per column, across multiple columns? Or is that all being done in report setup?

Thank you!
Parents
  • $organization

    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

Reply Children
No Data