All geek questions in one place

T-SQL | Manipulation String and Aggregation

I have the following scenario.

SOURCE TABLE 1

CREATE TABLE #Table1 ( Div varchar(10), Dept varchar(10), States varchar(10) ) INSERT INTO #Table1 SELECT 'Div1','Dept1','CA,NV,TX' UNION ALL SELECT 'Div2','Dept2','MI,OH,IN' UNION ALL SELECT 'Div3','Dept2','NY,NJ,PA' UNION ALL SELECT 'Div4','Dept1',NULL

SOURCE TABLE 2

 CREATE TABLE #Table2 ( Div varchar(10), Dept varchar(10), States varchar(10) ) INSERT INTO #Table2 SELECT 'Div1','Dept1','CA' UNION ALL SELECT 'Div1','Dept1','NV, TX' UNION ALL SELECT 'Div1','Dept1','TX, CA' UNION ALL SELECT 'Div1','Dept1','CA, NV' UNION ALL SELECT 'Div2','Dept2','MI, OH' UNION ALL SELECT 'Div2','Dept2','MI, IN' UNION ALL SELECT 'Div2','Dept2','OH' UNION ALL SELECT 'Div3','Dept2','NY, NJ, PA'

DESIRED EXIT

 CREATE TABLE #Table3 ( Div varchar(10), Dept varchar(10), States varchar(50) ) INSERT INTO #Table3 SELECT 'Div1','Dept1','CA - (3), NV - (2), TX - (2)' UNION ALL SELECT 'Div2','Dept2','MI - (2), OH - (2), IN - (1)' UNION ALL SELECT 'Div3','Dept2','NY - (1), NJ - (1), PA - (1)' UNION ALL SELECT 'Div4','Dept1',NULL SELECT * FROM #Table1 SELECT * FROM #Table2 SELECT * FROM #Table3 DROP TABLE #Table1 DROP TABLE #Table2 DROP TABLE #Table3

SQLFIDDLE

Goal. Based on #Table1 and #Table2 , combine the two tables in the Div and Dept fields, and then compile the counts for different states in the States field and create an output where you have Div , Dept and States with a unique counter for each of these states printed next to with condition.

I am not sure how to achieve this. I am trying LIKE but cannot figure out how to make it dynamic. I will continue to try to find out if I can find out. Thought I'd put this question here and see if I can help.

thanks

UPDATE:

Desired output

 Div Dept States Div1 Dept1 CA - (3), NV - (2), TX - (2) Div2 Dept2 MI - (2), OH - (2), IN - (1) Div3 Dept2 NY - (1), NJ - (1), PA - (1) Div4 Dept1 NULL
+5
source share
3 answers

Your requirements are very unpleasant, but since we need to work with the developers of what we have. Here's a widely used solution using the Common Table (CTE) expression:

 ;WITH CTE1 AS ( SELECT Div, Dept, REPLACE(States,' ','') + ',' AS States FROM Table2 ), CTE2 AS ( SELECT c1.Div, c1.Dept, LEFT(c1.States,CHARINDEX(',', c1.States)-1) AS IndividualState, RIGHT(c1.States,LEN(c1.States)-CHARINDEX(',', c1.States)) AS RemainingStates FROM CTE1 c1 UNION ALL SELECT c2.Div, c2.Dept, LEFT(c2.RemainingStates,CHARINDEX(',', c2.RemainingStates)-1), RIGHT(c2.RemainingStates,LEN(c2.RemainingStates) - CHARINDEX(',', c2.RemainingStates)) FROM CTE2 c2 WHERE LEN(c2.RemainingStates) > 0 ), CTE3 AS ( SELECT Div, Dept, IndividualState, COUNT(*) AS StateCount FROM CTE2 GROUP BY Div, Dept, IndividualState ), CTE4 AS ( SELECT t1.Div, t1.Dept, ( SELECT c3.IndividualState + ' - (' + CONVERT(varchar(10),c3.StateCount) + '), ' FROM CTE3 c3 WHERE c3.Div = t1.Div AND c3.Dept = t1.Dept FOR XML PATH('') ) AS States FROM Table1 t1 ) SELECT Div, Dept, LEFT(States, LEN(States) - 1) AS States FROM CTE4

Explanation

  • CTE1 clears data in Table2 : removes spaces, adds a comma to the end
  • CTE2 normalization performed
  • CTE3 running count
  • CTE4 does final assembly by putting CA | 3 CA | 3 in CA - (3), ...

The last SELECT removes the trailing comma for cleaner output.

To better understand each step, you can replace the final SELECT with SELECT * FROM CTE1 , SELECT * FROM CTE2 , etc.

+6
source

So, first of all you need to separate the concatenated values ​​in #Temp1 and #Temp2 . There are various methods for this, I will use the numbers table described in this terrible blog from Aaron Bertrand. So, we need a table of numbers, which can be performed as follows:

 ;WITH n AS ( SELECT x = ROW_NUMBER() OVER (ORDER BY s1.[object_id]) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2 ) SELECT Number = x INTO #Numbers FROM n WHERE x BETWEEN 1 AND 8000;

Then you will need to do the split, and then the group concatenation method for your result:

 ;WITH T1 AS ( SELECT * FROM #Table1 T OUTER APPLY (SELECT Item = SUBSTRING(T.States, Number, CHARINDEX(',',T.States + ',', Number) - Number) FROM #Numbers WHERE Number <= CONVERT(INT, LEN(T.States)) AND SUBSTRING(',' + T.States, Number, LEN(',')) = ',') N ), T2 AS ( SELECT * FROM #Table2 T OUTER APPLY (SELECT Item = SUBSTRING(T.States, Number, CHARINDEX(', ',T.States + ', ', Number) - Number) FROM #Numbers WHERE Number <= CONVERT(INT, LEN(T.States)) AND SUBSTRING(', ' + T.States, Number, LEN(', ')) = ', ') N ), T3 AS ( SELECT T1.Div, T1.Dept, T1.Item, COUNT(*) N FROM T1 LEFT JOIN T2 ON T1.Div = T2.Div AND T1.Dept = T2.Dept AND T1.Item = T2.Item GROUP BY T1.Div, T1.Dept, T1.Item ) SELECT A.Div, A.Dept, States = STUFF((SELECT ',' + CONVERT(VARCHAR(20), Item) + ' - (' + CAST(N AS VARCHAR(4)) + ')' FROM T3 WHERE Div = A.Div AND Dept = A.Dept FOR XML PATH(''), TYPE).value('.[1]','nvarchar(max)'),1,1,'') FROM T3 A ORDER BY Div, Dept, Item

Results:

 ╔══════╦═══════╦════════════════════════════╗ ║ Div ║ Dept ║ States ║ ╠══════╬═══════╬════════════════════════════╣ ║ Div1 ║ Dept1 ║ CA - (3),NV - (2),TX - (2) ║ ║ Div1 ║ Dept1 ║ CA - (3),NV - (2),TX - (2) ║ ║ Div1 ║ Dept1 ║ CA - (3),NV - (2),TX - (2) ║ ║ Div2 ║ Dept2 ║ IN - (1),MI - (2),OH - (2) ║ ║ Div2 ║ Dept2 ║ IN - (1),MI - (2),OH - (2) ║ ║ Div2 ║ Dept2 ║ IN - (1),MI - (2),OH - (2) ║ ║ Div3 ║ Dept2 ║ NJ - (1),NY - (1),PA - (1) ║ ║ Div3 ║ Dept2 ║ NJ - (1),NY - (1),PA - (1) ║ ║ Div3 ║ Dept2 ║ NJ - (1),NY - (1),PA - (1) ║ ║ Div4 ║ Dept1 ║ NULL ║ ╚══════╩═══════╩════════════════════════════╝
+6
source

Ideally, of course, this data would be normalized, because it is random. Given that you are stuck in this structure, I believe that it is best to use the driver status table or use one of the many separation functions available to separate shared values, and then use them to get each state is its own line:

 ;WITH cte AS (SELECT DISTINCT b.Div,b.Dept,a.abbrs FROM #States a RIGHT JOIN #Table1 b ON ','+REPLACE(b.States,' ','')+',' LIKE '%,'+a.abbrs+',%' ) ,cte2 AS (SELECT b.Div,b.Dept,a.abbrs FROM #States a JOIN #Table2 b ON ','+REPLACE(b.States,' ','')+',' LIKE '%,'+a.abbrs+',%' ) ,cte3 AS (SELECT a.Div,a.Dept,a.abbrs,CAST(COUNT(b.abbrs)AS VARCHAR(25)) CT FROM cte a LEFT JOIN cte2 b ON a.Dept = b.Dept AND a.Div = b.Div AND a.abbrs = b.abbrs GROUP BY a.div,a.dept,a.abbrs ) SELECT DISTINCT Div,Dept ,STUFF((SELECT DISTINCT ',' + abbrs+'-('+CT+')' FROM cte3 b WHERE a.Div = b.Div AND a.Dept = b.Dept FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') ,1,1,'') FROM cte3 a

Demo: SQL Fiddle

Note:

  • cte1 - creates a separate list div / dept / state from table1
  • cte2 - creates a list of all div / dept / states elements from table2
  • cte3 - aggregates in div / dept / state to get the score

Exit:

 | DIV | DEPT | STATES | |------|-------|----------------------| | Div1 | Dept1 | CA-(3),NV-(2),TX-(2) | | Div2 | Dept2 | IN-(1),MI-(2),OH-(2) | | Div3 | Dept2 | NJ-(1),NY-(1),PA-(1) | | Div4 | Dept1 | (null) |

Updated script to include your NULL string and added output.

+2
source

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

More articles:


All Articles