T-SQL : can this data be displayed using PIVOT without an aggregate-CodePudding

I have two tables that I've been asked to create a PIVOT table with however there is no aggregate and after messing around with this I'm unsure this is possible.

Table A looks like:

ID  CustomerID  ItemCode  CustomerItemCode  CustomerItemDescription
-------------------------------------------------------------------
1       1          123           321              product x
2       2          123           456              product x
3       1          987           789              product y
4       2          987           567              product y

Table B:

CustomerID  CustomerName
------------------------
    1       Customer ABC   
    2       Customer XYZ

What the result should look like is:

ItemCode  CustomerItemDescription  Customer ABC  Customer XYZ
-------------------------------------------------------------
  123        product x                  321           456
  987        product y                  789           567

Because customers could always be added I'm trying to make this as dynamic as possible so I've gotten as far as setting up the customer columns and creating a temp table with the data but without an aggregate I'm unsure how to make this display properly.

CodePudding user response：

That is an interesting one and yes it is possible.

Here two possibilities to achieve this.

Option 1 (the simpler to read and understand):

You can use this if you know the names of the Customers and can generate parts of the required query outside of SQL and execute the full in the end.

SELECT
  ItemCode,
  CustomerItemDescription,
  max(case when (CustomerName='Customer ABC') then CustomerItemCode else NULL end) as 'Customer ABC',
  max(case when (CustomerName='Customer XYZ') then CustomerItemCode else NULL end) as 'Customer XYZ'
FROM Table_A
JOIN Table_B ON Table_A.CustomerID = Table_B.CustomerID
GROUP BY ItemCode, CustomerItemDescription
ORDER BY ItemCode;

SQL-Fiddle for Option 1: https://www.db-fiddle.com/f/eEEDSao6Qy9v6um8N4zjqn/0

Option 2 (dynamic but hard to understand):

Here you generates dynamic the query inside of the SQL using variabel and execute this in the end.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when (CustomerName = ''',
      CustomerName,
      ''') then CustomerItemCode else NULL end) as ''',
      CustomerName,''''
    )
  ) INTO @sql
FROM Table_A
JOIN Table_B ON Table_A.CustomerID = Table_B.CustomerID;

SET @sql = CONCAT('SELECT ItemCode, CustomerItemDescription, ', @sql, ' 
                  FROM Table_A
                  JOIN Table_B ON Table_A.CustomerID = Table_B.CustomerID
                  GROUP BY ItemCode, CustomerItemDescription
                  ORDER BY ItemCode');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL-Fiddle for Option 2: https://www.db-fiddle.com/f/xkAaZM9Z7Wszh89PMYNYWP/0

Check also this article for additional infos: https://ubiq.co/database-blog/display-row-values-columns-mysql/

SQL-Fiddle for the Article: https://www.db-fiddle.com/f/qZedfre2FtowmsxRB5TWt8/0

CodePudding user response：

Concept is right - use dynamic sql. If you are specifically working with T-SQL and SQL Server 2012 , try the following:

declare @fldlist varchar(max) = stuff((
       select ', '  concat('max(case when customername = ''', CustomerName , ''' then CustomerItemCode else NULL end) [', CustomerName , ']') 
           from Table_B 
           JOIN Table_B ON Table_A.CustomerID = Table_B.CustomerID for xml path('')),1,1,'')

/*
   need to use a global temp table because dynamic sql
*/
declare @tbname varchar(255) = '##UnlikelyToBeUsedGlobalTempTblName';

/*
    there is an 8000 char limit for dynamic sql in sql server.  If there are too many customers with long names, you will need to split tables. 
*/
declare @sql varchar(8000) = CONCAT('
if object_id(''tempdb..''' @tbname ''') is not null drop table ',@tbname,'
SELECT ItemCode, CustomerItemDescription, ', @fldlist, ' into ' , @tbname , '
                  FROM Table_A
                  JOIN Table_B ON Table_A.CustomerID = Table_B.CustomerID
                  GROUP BY ItemCode, CustomerItemDescription
                  ORDER BY ItemCode');
exec(@sql)

select * from ##UnlikelyToBeUsedGlobalTempTblName