Original post address: http://community.9cbs.net/expert/topic/3338/3338785.xml? Temp = .9853022
Have table T1: ID | Place Time Level --- | ------------------ 1 | P1 T3 L1 2 | P1 T1 L1 3 | P3 T1 L2 4 | P2 T2 L3 5 | P1 T1 L1 6 | P2 T3 L3
Want to achieve:
Generate a combination of all items between fields, and count the number of records containing the combination (only a combination of data only)
1 combination item count p1 3 p2 2 .....
2 combinations Item count p1_t1 2 p1_t3 1 ....
3 combinations Item count p1_t1_l1 2 p1_t1_l2 1 ....
-------------------------------------------------- ---------------------
- Example
- Example Data Create Table T1 (ID INT, Place Varchar (10), Time Varchar (10), Level Varchar (10), AA VARCHAR (10)) Insert T1 SELECT 1, 'P1', 'T3', 'L1 ',' Aa'Union All SELECT 2, 'P1', 'T1', 'L1', 'B'Union All Select 3,' P3 ',' T1 ',' L2 ',' Aa'Union All Select 4, 'P2', 'T2', 'L3', 'Aa'Union All Select 5,' P1 ',' T1 ',' L1 ',' Aa'Union All Slect 6, 'P2', 'T3', 'L3 ',' bb'go
- Universal processing store process create proc p_qry @ count int = 1 - combined number of items asDeclare @sa nvarchar (4000), @ SB nvarchar (4000) Declare @ s2 nvarchar (4000), @ s3 nvarchar (4000) Declare @S varchar (8000)
IF isnull (@ count, 0) <0 set @ count = 1select a = name, b = colidinto #t from syscolumns where id = Object_id (n't1 ') and name <>' ID'set @ count = Case When @ Count> @@ rowcount kilone @@ rowcount else @count end
IF @ count = 1 set @ sa = 'select @ s = @ s ' 'union all select item = [' ' a ' '], [count] = count (*) from t1 group by [' ' a " ']' from # t'elsebegin select @ sa = 'select @ s = @ s ' 'union all select item = [' ' a.a ' ']' ', @ SB =' '' ['' a.a ''] '', @ S2 = 'from #t a', @ S3 = 'where a.b' while @count> 1 SELECT @ count = @ count-1, @ sa = @ sa ' '' '' '' ' CHAR (@ count / 26 97) char (@ count% 26 97) '. A ''] '', @ SB = @ SB ' ' ', [' ' ' CHAR (@ County / 26 97) Char (@ count% 26 97) '. A ' ']' ', @ S2 = @ S2 ', # T' Char (@ count / 26 97) char (@ count% 26 97), @ S3 = @ S3 '<' char (@ count / 26 97) char (@ COUNT% 26 97) '. b' 'and' char (@ count / 26 97) char (@ count% 26 97) '. b' select @ sa = @ sa ' ' ', [count] = count (*) from t1 group by' ' ' @ SB '' @ S2 '' Left (@ S3, LEN (@ S3) -9) endset @S = '' EXEC SP_EXECUTESQL @ sa, n '@ s varchar (8000) OUT', @ s outset @ s = stuff (@ s, 1 ,11, '') exec (@S) Go
- Call EXEC P_QRY 3GO
- Delete Test DROP TABLE T1DROP PROC P_QRY
