All geek questions in one place

Sql Server 2008 - PIVOT without aggregation function

I know that you have several topics affecting this. But I did not find what met my needs. I need (on demand) to collapse the data of a deep table into a table with a wide output. This is that I cannot use the aggregate with Pivot because it eats the answers that are needed for the output. I worked on a solution, but I don’t think it is better, because it will require numerous left connections to work. I have included all attempts and notes as follows:

 - Sql Server 2008 db.  - Deep table structure (not subject to modification) contains name / value pairs with a userId as - foreign key.  In many cases there can be MORE THAN ONE itemValue given by the user for the - itemName such as if asked their race, can answer White + Hispanic, etc.  Each response is stored - as a seperate record - this cannot currently be changed.  - Goal: pivot deep data to wide while also compressing result - set down.  Account for all items per userId, and duplicating - column values ​​(rather than show nulls) as applicable - Sample table to store some data of both single and multiple responses DECLARE @testTable AS TABLE (userId int, itemName varchar (50), itemValue varchar (255)) INSERT INTO @testTable SELECT 1, 'q01', '1-q01 Answer' UNION SELECT 1, 'q02', '1-q02 Answer' UNION SELECT 1, 'q03', '1-q03 Answer 1 'UNION SELECT 1,' q03 ',' 1-q03 Answer 2 'UNION SELECT 1,' q03 ',' 1-q03 Answer 3 'UNION SELECT 1,' q04 ',' 1-q04 Answer 'UNION SELECT 1, 'q05', '1-q05 Answer' UNION SELECT 2, 'q01', '2-q01 Answer' UNION SELECT 2, 'q02', '2-q02 Answer' UNION SELECT 2, 'q03', '2-q03 Answer 1 'UNION SELECT 2,' q03 ',' 2-q03 Answer 2 'UNION SELECT 2,' q04 ',' 2-q04 Answer 'UNION SELECT 2,' q05 ',' 2-q05 Answer 'SELECT' Raw Data 'SELECT * FROM @TestTable SELECT' Using Pivot - shows aggregate result of itemValue per itemName - eats others';  WITH Data AS (SELECT [userId], [itemName], [itemValue] FROM @testTable) SELECT [userId], [q02], [q03], [q05] FROM Data PIVOT (MIN (itemValue) - Aggregate function eats needed values. FOR itemName in ([q02], [q03], [q05])) AS PivotTable SELECT 'Aggregate with Grouping - Causes Null Values' SELECT DISTINCT userId, [q02] = Max (CASE WHEN itemName = 'q02' THEN itemValue END), [q03] = Max (CASE WHEN itemName = 'q03' THEN itemValue END), [q05] = Max (CASE WHEN itemName = 'q05' THEN itemValue END) FROM @testTable WHERE itemName in ('q02', ' q03 ',' q05 ') - Makes it a hair quicker GROUP BY userId - If by userId only, it only gives 1 row PERIOD = BAD !!  , [itemName], [itemValue] SELECT 'Multiple Left Joins - works properly but bad if pivoting 175 columns or so';  WITH Data AS (SELECT userId, [itemName], [itemValue] FROM @testTable WHERE itemName in ('q02', 'q03', 'q05') - Makes it a hair quicker) SELECT DISTINCT s1.userId, [q02] = s2. [itemValue], [q03] = s3. [itemValue], [q05] = s5. [itemValue] FROM Data s1 LEFT JOIN Data s2 ON s2.userId = s1.userId AND s2. [itemName] = 'q02 'LEFT JOIN Data s3 ON s3.userId AND s3. [ItemName] =' q03 'LEFT JOIN Data s5 ON s5.userId = s1.userId AND s5. [ItemName] =' q05 '

So the bottom query is the only (for now) that does what I need, but the LEFT JOIN WIL gets out of hand and causes performance issues when I use the actual element names to rotate. Any recommendations are appreciated.

+6
source share
4 answers
  ;  WITH SRData AS (
     SELECT - Only query single response items in this block
         [userId]
         , [q01]
         , [q02]
         , [q04]
         , [q05]
     FROM
         @testTable
     PIVOT
     (
         MIN (itemValue) 
         FOR itemName in ([q01], [q02], [q04], [q05])
     ) AS PivotTable
 )
 SELECT
     sr. [userId]
     , sr. [q01]
     , sr. [q02]  
     , [q03] = mr03. [itemValue]
     , sr. [q04]      
     , sr. [q05]      
     , [q06] = mr06. [itemValue]
 FROM
     Srdata sr
     LEFT JOIN @testTable mr03 ON mr03.userId = sr.userId AND mr03.itemName = 'q03' - Muli Response for q03
     LEFT JOIN @testTable mr06 ON mr06.userId = sr.userId AND mr06.itemName = 'q06' - Muli Response for q06
+3
source

I think you will have to stick with unions, because unions are exactly how to create results like the ones you do. The purpose of combining is to combine sets of strings (with or without conditions), and your target result is nothing more than a combination of subsets of strings.

However, if most questions always have single answers, you can significantly reduce the number of associations needed. The idea is to combine only groups with multiple answers in separate sets of rows. As for elements with one answer, they are combined only as part of the entire data set of the target objects.

An example should better illustrate what I could poorly describe verbally. Assuming the source data there are two groups with many answers, 'q03' and 'q06' (in fact, here is the source table:

 DECLARE @testTable AS TABLE( userId int, itemName varchar(50), itemValue varchar(255) ); INSERT INTO @testTable SELECT 1, 'q01', '1-q01 Answer' UNION SELECT 1, 'q02', '1-q02 Answer' UNION SELECT 1, 'q03', '1-q03 Answer 1' UNION SELECT 1, 'q03', '1-q03 Answer 2' UNION SELECT 1, 'q03', '1-q03 Answer 3' UNION SELECT 1, 'q04', '1-q04 Answer' UNION SELECT 1, 'q05', '1-q05 Answer' UNION SELECT 1, 'q06', '1-q06 Answer 1' UNION SELECT 1, 'q06', '1-q06 Answer 2' UNION SELECT 1, 'q06', '1-q06 Answer 3' UNION SELECT 2, 'q01', '2-q01 Answer' UNION SELECT 2, 'q02', '2-q02 Answer' UNION SELECT 2, 'q03', '2-q03 Answer 1' UNION SELECT 2, 'q03', '2-q03 Answer 2' UNION SELECT 2, 'q04', '2-q04 Answer' UNION SELECT 2, 'q05', '2-q05 Answer' UNION SELECT 2, 'q06', '2-q06 Answer 1' UNION SELECT 2, 'q06', '2-q06 Answer 2' ;

which is similar to the table in the original message, but with the added elements 'q06' ), the resulting script could be like this:

 WITH ranked AS ( SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY userId, itemName ORDER BY itemValue) FROM @testTable ), multiplied AS ( SELECT r.userId, r.itemName, r.itemValue, rn03 = r03.rn, rn06 = r06.rn FROM ranked r03 INNER JOIN ranked r06 ON r03.userId = r06.userId AND r06.itemName = 'q06' INNER JOIN ranked r ON r03.userId = r.userId AND ( r.itemName = 'q03' AND r.rn = r03.rn OR r.itemName = 'q06' AND r.rn = r06.rn OR r.itemName NOT IN ('q03', 'q06') ) WHERE r03.itemName = 'q03' AND r.itemName IN ('q02', 'q03', 'q05', 'q06') ) SELECT userId, rn03, rn06, q02, q03, q05, q06 FROM multiplied PIVOT ( MIN(itemValue) FOR itemName in (q02, q03, q05, q06) ) AS PivotTable
+3
source

Note. If you have a separate table with questions for the user (where userId + itemName is the key to the first / candidate), you can delete the first CTE (UserQuestion) and use this table in the second CTE (UserQuestionWithAllAnswers) instead of UserQuestion CTE:

 ;WITH UserQuestionWithAllAnswers AS ( SELECT a.userId ,a.itemName ,ca.AllAnswers FROM TableUserQuestion a ...

Note 2: Another method may be a CLR stored procedure.

Performance is not fantastic, but if you want to see all the answers for each user and the question, then this request may be the solution:

 ;WITH UserQuestion AS ( SELECT x.userId ,x.itemName FROM @testTable x GROUP BY x.userId, x.itemName ), UserQuestionWithAllAnswers AS ( SELECT a.userId ,a.itemName ,ca.AllAnswers FROM UserQuestion a CROSS APPLY ( SELECT SUBSTRING( (SELECT ','+b.itemValue FROM @testTable b WHERE a.userId = b.userId AND a.itemName = b.itemName FOR XML PATH('')) ,2 ,8000) AS AllAnswers ) ca ) SELECT pvt.* FROM UserQuestionWithAllAnswers src PIVOT ( MIN(src.AllAnswers) FOR itemName IN ([q01], [q02], [q03], [q04], [q05]) ) AS pvt;

Results:

 userId q01 q02 q03 q04 q05 ------ ------------ ------------ -------------------------------------------- ------------ ------------- 1 1-q01 Answer 1-q02 Answer 1-q03 Answer 1,1-q03 Answer 2,1-q03 Answer 3 1-q04 Answer 1-q05 Answer 2 2-q01 Answer 2-q02 Answer 2-q03 Answer 1,2-q03 Answer 2 2-q04 Answer 2-q05 Answer
+2
source

It’s not clear what the desired results should look exactly, but one possibility

 ; WITH Data AS ( SELECT ROW_NUMBER() OVER (PARTITION BY [userId], [itemName] ORDER BY [itemValue]) AS RN , [userId] , [itemName] , [itemValue] FROM @testTable ) SELECT [userId] , [q02] , [q03] , [q05] FROM Data PIVOT ( MIN(itemValue) FOR itemName in ([q02], [q03], [q05]) ) AS PivotTable

Returns

 userId q02 q03 q05 ----------- ------------------------------ ------------------------------ ------------------------------ 1 1-q02 Answer 1-q03 Answer 1 1-q05 Answer 1 NULL 1-q03 Answer 2 NULL 1 NULL 1-q03 Answer 3 NULL 2 2-q02 Answer 2-q03 Answer 1 2-q05 Answer 2 NULL 2-q03 Answer 2 NULL
+2
source

Source: https://habr.com/ru/post/899822/

More articles:


All Articles