October 15, 2018 at 5:59 am
I have a table with 'words', each row contains a word, a position, a linenumber and a textnumber.
I want to concatenate the words into lines.
I want to concatenate the lines into texts.
In steps I can do this with 'FOR XML' or with CTE. (Step one; concatenate the words, step two; concatenate the words.)
I would like this in a single WITH (CTE with recursion) construct, in two distinct steps.
Is this possible ?
Thanks for your time and attention.
Ben
(The real objects are not words, lines and texts, but three different objects but with a similar structure).
October 15, 2018 at 6:01 am
ben.brugman - Monday, October 15, 2018 5:59 AMI have a table with 'words', each row contains a word, a position, a linenumber and a textnumber.
I want to concatenate the words into lines.
I want to concatenate the lines into texts.In steps I can do this with 'FOR XML' or with CTE. (Step one; concatenate the words, step two; concatenate the words.)
I would like this in a single WITH (CTE with recursion) construct, in two distinct steps.
Is this possible ?Thanks for your time and attention.
Ben(The real objects are not words, lines and texts, but three different objects but with a similar structure).
Can we have some sample data please Ben? Cheers.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 15, 2018 at 8:43 am
ChrisM@Work - Monday, October 15, 2018 6:01 AMCan we have some sample data please Ben? Cheers.
Some Sample data:
-- Sample data.
-- rij_number and color are the two fields which represent words and should be in the endresult.
--
-- First concatenation:
-- ON Number and letter, ORDER rij_number. Example ;
-- number letter List_Of_Colors
-- 1 E (22613 = green), (476352 = blue), (914356 = yellow)
-- 5 A (10252 = blue), (542769 = amber), (803775 = octarine)
-- 5 D (43339 = green), (182079 = blue), (263843 = amber), (659097 = octarine), (775327 = blue)
-- Second concatenation on:
-- ON Number, ORDER is on letter, letter is part of the concatenation. Example number=5 and the concatenated field:
-- number Lines_of_lists
-- 5 A = ( (10252 = blue), (542769 = amber), (803775 = octarine))<EOL> , D = ( (43339 = green), (182079 = blue), (263843 = amber), (659097 = octarine), (775327 = blue))<EOL> , E = ( (854873 = octarine), (863866 = amber))<EOL>
-
- exec sp_drop S_colors -- (Drops the table if the table exists for older versions. Sorry).
DROP TABLE IF EXISTS S_colors -- For more recent versions of SQLserver
;
With
N as (select 1 as number union select 2 union select 3 union select 4 union select 5 union select 7), -- No 6
N1 as (select na.number from N NA, N NB, N NC, N ND, N NE),
N2 as (select na.number from N1 NA),
L as (select 'A' as letter union select 'B' as letter union select 'C' as letter union select 'D' as letter union select 'E' as letter),
M as (select 12.4 as mass union select 2.4 as mass union select 1.4 as mass union select 12 as mass union select 42 as mass),
C as (select 'blue' as color union select 'yellow' as color union select 'green' as color union select 'amber' as color union select 'octarine' as color),
R as (select *,convert(integer,(ROUND(rand(checksum(newid()))* 10000,0,1))) as value
,ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY (SELECT NULL)) RIJ_NUMMER
from n2,l,m,c),
s as (select * from R where value >= 9999 -- Large = 9900 Medium = 9990
-- or (number= 4 and letter = 'D') -- One very large occurance, comment out for small example.
) -- create a selection
select * INTO S_colors from s
This produces a 'small' set of date.
It's also possible to generate a medium or large set of data.
The number 4 and letter D can be used to generate one very large row in the end result.
Data to start with (10 sample rows) different on each generation :
number letter mass color value RIJ_NUMMER
5 A 2.4 blue 9999 10252
1 E 1.4 green 9999 22613
7 E 1.4 yellow 9999 34483
5 D 12.0 green 9999 43339
7 D 2.4 blue 9999 70452
7 D 1.4 amber 9999 111718
7 E 12.4 blue 9999 179976
5 D 12.0 blue 9999 182079
5 D 1.4 amber 9999 263843
7 E 12.4 green 9999 272236
One row (of 3) from the end result:
5
A = ( (10252 = blue), (542769 = amber), (803775 = octarine))<EOL> ,
D = ( (43339 = green), (182079 = blue), (263843 = amber), (659097 = octarine), (775327 = blue))<EOL> ,
E = ( (854873 = octarine), (863866 = amber))<EOL>
<EOL> is a marker for EndOfLine.
This is assembled in some haste, so I hope I have done this correctly.
Thanks for your time and attention,
Ben
October 15, 2018 at 2:07 pm
It's not pretty, and it may not be efficient, but this does produce something CLOSE to what you are expecting.
; WITH CTE AS
(
SELECT number, letter + ' = (' AS letter, ', (' + CAST(RIJ_NUMMER AS VARCHAR(15)) + ' = ' + color + ')' AS word, ')<EOL>' AS close_paren
, ROW_NUMBER() OVER(ORDER BY number, letter, RIJ_NUMMER, color) AS rn
, DENSE_RANK() OVER(ORDER BY number, letter) AS dr
FROM #S_colors
)
SELECT
(
SELECT
CASE
WHEN GROUPING(letter) = 0 THEN NULL ELSE number END,
CHAR(10),
CASE WHEN GROUPING(close_paren) = 0 THEN NULL ELSE letter END,
CASE WHEN MIN(rn) = 1 THEN STUFF(word, 1, 2, '') ELSE word END,
close_paren + CHAR(10)
FROM CTE
GROUP BY GROUPING SETS((number), (number, letter), (word), (number, letter, close_paren))
ORDER BY MIN(dr), GROUPING(close_paren) DESC, GROUPING(number), GROUPING(letter) DESC, letter, GROUPING(word), MIN(rn)
FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(MAX)')
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 16, 2018 at 2:17 am
drew.allen - Monday, October 15, 2018 2:07 PMIt's not pretty, and it may not be efficient, but this does produce something CLOSE to what you are expecting.
Drew
Thanks,
I'll have to study this to understand this. I did run the query, it did run (changing one character of the tablename 🙂 ). The result produces only one row, where there should have been three six rows, with the example.
I had hoped for a construction without the 'FOR XML' and without intermediate tables. (My solution has an intermediate table and two FOR XML constructs :crazy: )
Still hoping for a CTE (without the 'FOR XML'), without intermediate tables.
Thanks for your solution, I will spend some time on it and learn from it.
Thanks,
Ben
October 16, 2018 at 12:50 pm
ben.brugman - Tuesday, October 16, 2018 2:17 AMdrew.allen - Monday, October 15, 2018 2:07 PMIt's not pretty, and it may not be efficient, but this does produce something CLOSE to what you are expecting.
DrewThanks,
I'll have to study this to understand this. I did run the query, it did run (changing one character of the tablename 🙂 ). The result produces only one row, where there should have beenthreesix rows, with the example.I had hoped for a construction without the 'FOR XML' and without intermediate tables. (My solution has an intermediate table and two FOR XML constructs :crazy: )
Still hoping for a CTE (without the 'FOR XML'), without intermediate tables.
Thanks for your solution, I will spend some time on it and learn from it.
Thanks,
Ben
My solution doesn't use any intermediate tables. The only table I used was a temp table (changed from the permanent table in your creation script).
Part of the problem with producing the results you wanted, is that (a) you have a script that randomly generates sample data, so we can't match your sample data without typing it in, and (b) your sample results DO NOT match your sample data.
And what do you have against FOR XML? It's very fast.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 16, 2018 at 2:20 pm
drew.allen - Tuesday, October 16, 2018 12:50 PMMy solution doesn't use any intermediate tables. The only table I used was a temp table (changed from the permanent table in your creation script).Part of the problem with producing the results you wanted, is that (a) you have a script that randomly generates sample data, so we can't match your sample data without typing it in, and (b) your sample results DO NOT match your sample data.
And what do you have against FOR XML? It's very fast.
Drew
1.
No intermediate tables. Thanks.
2.
Although the data is randomly generated, the end results I had were all very similar. With all tests I did run the number of rows was always the a row for each number (1,2,3,4,5,7), there was always enough data in the set to create at least one entry for each number. The code is build to generate very different amounts of data and even generate a special case (4,D) which is generates a huge row. (In the end result part of the number 4 row). So even with randomly generated data the end result should still be fairly consistently 6 rows. (Statistically in rare cases the number of rows could be 5, and very rare 4 etc.). The code serves it purpose to create different datasets in size, also sizes beyond what is reasonable to post here. (Tried to use sp_generate_inserts for sending the data, but that was large for the small dataset and impractical for a large sample).
Tested your solution on a fairly large dataset, and your solution is fast. With the large row (4,D) present and the where clause on 9900. Your code ran in two seconds on a fairly slow machine. This is fast for concatenating that amount of data.
3.
And what do you have against FOR XML? It's very fast.
It's very fast especially for a large number of concatenations. And still works were other solutions take extremely long or use a lot of diskspace or both.
Greatest problem is that I do not understand the 'FOR XML' completely. :blush::sick: For me it's very convoluted. (Sorry). I tried to alter your solution so that the result gets written to a table. (Did not succeed). (Tried to put the end query as a select in the With statement and then use the result to be put in a table. Tried a number of variations leaving things out and/or removing parts. As said did not succeed).
With solutions I have build with 'FOR XML', the special XML symbols had to be taken care of > < &emp; and this has to be done carefully because if there is a distinction in a text between > and a >. But that might be part of my misunderstanding.
Depending on the construct you get 'real' XML tagging. (But this can be avoided fairly easily).
And concatenation is not have anything to do with XML, so 'FOR XML' is not elegant.
Very often constructions with 'FOR XML' do not look logically, do not look simply or straitforward. Even when building a XML file it is difficult to use.
If you make a mistake, you do not get the 'wrong' output which you can correct, you get an error message which (to me) does not always explain to me what I have done wrong.
And even in this community, even in applicable cases is rarely suggested.
I'll put up the solution with the intermediate tables (and end table here). Your code correctly works with special symbols, were my code has to repair the special symbols (twice). So maybe you could point out to me what I am doing wrong. At the moment I am working from home, it late and do not have access to SQLserver.
Thanks for your time. And educating me.
Ben
October 16, 2018 at 2:47 pm
ben.brugman - Tuesday, October 16, 2018 2:20 PM2.
Although the data is randomly generated, the end results I had were all very similar. With all tests I did run the number of rows was always the a row for each number (1,2,3,4,5,7), there was always enough data in the set to create at least one entry for each number. The code is build to generate very different amounts of data and even generate a special case (4,D) which is generates a huge row. (In the end result part of the number 4 row). So even with randomly generated data the end result should still be fairly consistently 6 rows. (Statistically in rare cases the number of rows could be 5, and very rare 4 etc.). The code serves it purpose to create different datasets in size, also sizes beyond what is reasonable to post here. (Tried to use sp_generate_inserts for sending the data, but that was large for the small dataset and impractical for a large sample).Tested your solution on a fairly large dataset, and your solution is fast. With the large row (4,D) present and the where clause on 9900. Your code ran in two seconds on a fairly slow machine. This is fast for concatenating that amount of data.
I understand that, but it also helps in development to have a small static dataset with a given result. You said that your result should have multiple rows, but it's not clear where those rows should break.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 17, 2018 at 4:05 am
drew.allen - Tuesday, October 16, 2018 2:47 PMI understand that, but it also helps in development to have a small static dataset with a given result. You said that your result should have multiple rows, but it's not clear where those rows should break.Drew
-- Second concatenation on:
-- ON Number, ORDER is on letter, letter is part of the concatenation. Example number=5 and the concatenated field:
-- number Lines_of_lists
-- 5 A = ( (10252 = blue), (542769 = amber), (803775 = octarine))<EOL> , D = ( (43339 = green), (182079 = blue), (263843 = amber), (659097 = octarine), (775327 = blue))<EOL> , E = ( (854873 = octarine), (863866 = amber))<EOL> N as (select 1 as number union select 2 union select 3 union select 4 union select 5 union select 7),
Sorry I was not clear enough. There are 6 numbers (1,2,3,4,5,7) here, each number should have a row in the endresult. Notice there is no 6 in the set.
(In my code I removed 3 of the six numbers to make the set smaller, but that did not get in the sample given in this thread.).
I'll try to produce a small static data set today.
(And the result for that).
Thanks,
Ben
October 18, 2018 at 12:37 am
A static data set :
(delete s_colors -- if there is data from the generation of data).
--INSERTs generated by 'sp_generate_inserts' stored procedure written by Vyas
--Build number: 22
--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com
--http://vyaskn.tripod.com
SET NOCOUNT ON
PRINT 'Inserting values into [s_colors]'
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(5,'D',12.0,'amber',9999,11844)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(3,'A',2.4,'green',9999,22012)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'D',1.4,'amber',9999,43718)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(5,'E',1.4,'green',9999,59863)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(1,'E',1.4,'yellow',9999,86358)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(3,'D',1.4,'green',9999,95588)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'C',1.4,'octarine',9999,99198)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'C',12.0,'yellow',9999,103684)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(3,'E',1.4,'amber',9999,111368)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(5,'B',12.4,'blue',9999,128026)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(5,'A',1.4,'yellow',9999,135508)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(7,'B',2.4,'yellow',9999,167157)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'A',12.0,'green',9999,168139)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'C',1.4,'yellow',9999,179433)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(7,'C',12.0,'green',9999,199439)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'B',12.4,'octarine',9999,215421)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(4,'B',42.0,'yellow',9999,235160)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'A',2.4,'octarine',9999,238647)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(3,'C',2.4,'yellow',9999,252307)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(4,'E',2.4,'blue',9999,265977)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(4,'D',1.4,'green',9999,266713)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(5,'B',12.4,'yellow',9999,274281)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(1,'B',1.4,'yellow',9999,297033)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(1,'B',42.0,'blue',9999,304530)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(3,'A',2.4,'yellow',9999,317507)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(1,'C',42.0,'blue',9999,331555)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(7,'B',12.4,'octarine',9999,338171)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(5,'C',12.0,'blue',9999,352304)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(1,'C',42.0,'octarine',9999,362325)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(5,'C',1.4,'amber',9999,398818)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(4,'D',2.4,'yellow',9999,417457)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(7,'A',42.0,'yellow',9999,422885)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(1,'C',12.0,'octarine',9999,423074)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'D',2.4,'green',9999,424712)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(3,'A',42.0,'octarine',9999,424775)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(1,'A',12.4,'octarine',9999,434271)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(1,'A',42.0,'yellow',9999,443260)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(5,'E',42.0,'amber',9999,450620)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(7,'A',42.0,'octarine',9999,451400)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(5,'A',12.0,'amber',9999,455769)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(5,'A',12.0,'yellow',9999,460259)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(7,'E',2.4,'amber',9999,460492)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'B',12.0,'green',9999,479414)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(4,'B',2.4,'blue',9999,490152)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(1,'C',1.4,'green',9999,507063)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(4,'D',12.4,'green',9999,520211)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'C',42.0,'amber',9999,525195)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(4,'C',2.4,'green',9999,526187)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(1,'B',12.4,'yellow',9999,531031)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(4,'B',42.0,'blue',9999,533655)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(7,'E',1.4,'green',9999,534738)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'E',12.4,'amber',9999,542491)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'E',12.4,'amber',9999,544741)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(5,'B',2.4,'octarine',9999,565297)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(4,'C',2.4,'blue',9999,573427)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(4,'E',1.4,'octarine',9999,574248)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(4,'C',12.0,'green',9999,581689)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(7,'A',42.0,'amber',9999,606645)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'E',12.0,'octarine',9999,614499)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(7,'C',1.4,'yellow',9999,618683)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(3,'B',2.4,'green',9999,628787)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(7,'B',12.4,'blue',9999,662151)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'C',2.4,'amber',9999,667692)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(7,'C',1.4,'green',9999,675688)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(4,'C',12.0,'yellow',9999,676184)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(4,'B',1.4,'amber',9999,679168)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(7,'A',42.0,'octarine',9999,701150)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(5,'C',42.0,'blue',9999,703305)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'C',12.0,'green',9999,705189)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(1,'C',2.4,'amber',9999,727567)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'A',2.4,'green',9999,727637)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'D',12.0,'amber',9999,730719)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(1,'B',42.0,'amber',9999,753795)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'D',42.0,'yellow',9999,770460)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(4,'D',42.0,'yellow',9999,772960)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(7,'A',1.4,'yellow',9999,779133)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(4,'C',42.0,'blue',9999,782680)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(5,'E',1.4,'octarine',9999,797873)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'C',42.0,'green',9999,804940)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(1,'D',12.4,'amber',9999,813091)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(3,'B',12.0,'amber',9999,817794)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(7,'C',12.4,'octarine',9999,818196)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(4,'C',1.4,'octarine',9999,818698)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'C',2.4,'yellow',9999,819182)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(3,'B',1.4,'blue',9999,825278)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(1,'D',2.4,'amber',9999,833342)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(7,'D',42.0,'yellow',9999,855710)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(3,'B',12.4,'octarine',9999,878546)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(4,'C',12.0,'green',9999,890689)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(4,'A',12.4,'yellow',9999,898881)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(1,'C',12.4,'blue',9999,906801)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'A',12.0,'green',9999,907639)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(7,'B',42.0,'amber',9999,919420)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(7,'D',1.4,'octarine',9999,920973)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(3,'E',1.4,'amber',9999,942368)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(5,'B',12.4,'octarine',9999,949296)
INSERT INTO [s_colors] ([number],[letter],[mass],,[value],[RIJ_NUMMER])VALUES(2,'A',12.0,'green',9999,968389)
PRINT 'Done'
SET NOCOUNT OFF
The result of the static dataset ( 6 rows ):
number List_Of_Colors
----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 A = ( (434271 = octarine), (443260 = yellow))<EOL>
, B = ( (297033 = yellow), (304530 = blue), (531031 = yellow), (753795 = amber))<EOL>
, C = ( (331555 = blue), (362325 = octarine), (423074 = octarine), (507063 = green), (727567 = amber), (906801 = blue))<EOL>
, D = ( (813091 = amber), (833342 = amber))<EOL>
, E = ( (86358 = yellow))<EOL>
2 A = ( (168139 = green), (238647 = octarine), (727637 = green), (907639 = green), (968389 = green))<EOL>
, B = ( (215421 = octarine), (479414 = green))<EOL>
, C = ( (99198 = octarine), (103684 = yellow), (179433 = yellow), (525195 = amber), (667692 = amber), (705189 = green), (804940 = green), (819182 = yellow))<EOL>
, D = ( (43718 = amber), (424712 = green), (730719 = amber), (770460 = yellow))<EOL>
, E = ( (542491 = amber), (544741 = amber), (614499 = octarine))<EOL>
3 A = ( (22012 = green), (317507 = yellow), (424775 = octarine))<EOL>
, B = ( (628787 = green), (817794 = amber), (825278 = blue), (878546 = octarine))<EOL>
, C = ( (252307 = yellow))<EOL>
, D = ( (95588 = green))<EOL>
, E = ( (111368 = amber), (942368 = amber))<EOL>
4 A = ( (898881 = yellow))<EOL>
, B = ( (235160 = yellow), (490152 = blue), (533655 = blue), (679168 = amber))<EOL>
, C = ( (526187 = green), (573427 = blue), (581689 = green), (676184 = yellow), (782680 = blue), (818698 = octarine), (890689 = green))<EOL>
, D = ( (266713 = green), (417457 = yellow), (520211 = green), (772960 = yellow))<EOL>
, E = ( (265977 = blue), (574248 = octarine))<EOL>
5 A = ( (135508 = yellow), (455769 = amber), (460259 = yellow))<EOL>
, B = ( (128026 = blue), (274281 = yellow), (565297 = octarine), (949296 = octarine))<EOL>
, C = ( (352304 = blue), (398818 = amber), (703305 = blue))<EOL>
, D = ( (11844 = amber))<EOL>
, E = ( (59863 = green), (450620 = amber), (797873 = octarine))<EOL>
7 A = ( (422885 = yellow), (451400 = octarine), (606645 = amber), (701150 = octarine), (779133 = yellow))<EOL>
, B = ( (167157 = yellow), (338171 = octarine), (662151 = blue), (919420 = amber))<EOL>
, C = ( (199439 = green), (618683 = yellow), (675688 = green), (818196 = octarine))<EOL>
, D = ( (855710 = yellow), (920973 = octarine))<EOL>
, E = ( (460492 = amber), (534738 = green))<EOL>
(6 row(s) affected)
To make the end of lines (EOL) within a row more visible they are marked with the <EOL> symbol.
Ben
Code below.
Advantages:
It performs fast.
End result with multiple rows can be placed in a table.
Disadvantages;
Very difficult to read for people not familiar with the For XML code.
With two groupings I need an intermediate table.
Code as I have written needs to 'repair' the special XML symbols.
The complete set of code including (generation of data, a solution, and the solution by Drew.Allen:
(Remark Drew uses temporary tables, I have used persistent tables.)
(SP_Drop, drops a table if it exists, use the next line instead.)
exec sp_drop S_colors
-- DROP TABLE IF EXISTS S_colors -- Use this in resent SQLserver versions. SP_drop performs the same function in my systems.
;
With
N as (select 1 as number union select 2 union select 3 union select 4 union select 5 union select 7), -- No 6
N1 as (select na.number from N NA, N NB, N NC, N ND, N NE),
N2 as (select na.number from N1 NA),
L as (select 'A' as letter union select 'B' as letter union select 'C' as letter union select 'D' as letter union select 'E' as letter),
M as (select 12.4 as mass union select 2.4 as mass union select 1.4 as mass union select 12 as mass union select 42 as mass),
C as (select 'blue' as color union select 'yellow' as color union select 'green' as color union select 'amber' as color union select 'octarine' as color),
R as (select *,convert(integer,(ROUND(rand(checksum(newid()))* 10000,0,1))) as value
,ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY (SELECT NULL)) RIJ_NUMMER
from n2,l,m,c),
s as (select * from R where value >= 9999 -- Large = 9900 Medium = 9990
-- or (number= 4 and letter = 'D') -- One very large occurance, comment out for small example.
) -- create a selection
select * INTO S_colors from s
-- select * from C
-- select * from S_colors
select * from s_colors where number = 3 and letter = 'c' order by rij_nummer -- Small subset
select COUNT(*) tel, number, letter from s_colors group by number, letter order by tel desc
-- delete s_colors where number in (2,3,4) or letter in ('B','C') -- Use 999 and this line
-- Create a row for each number Letter combination with the color and rij_number concatenated.
---------------------------------------------------------------------------------
-- Create the first level of concatenation.
--
exec sp_drop S_list_of_colors
-- DROP TABLE IF EXISTS S_list_of_colors -- Use this in resent SQLserver versions. SP_drop performs the same function in my systems.
SELECT number, letter,
STUFF((SELECT ', (' + +CONVERT(varchar(8),rij_nummer)+' = '+color+')' AS [text()]
FROM S_colors C
WHERE
C.number = T.number and C.letter = t.letter order by rij_nummer
FOR XML PATH('') -- Select it as XML
), 1, 1, '' )
AS [List_Of_Colors] INTO S_list_of_colors
FROM s_colors T group by number, letter order by number, letter
-- select * from S_list_of_colors
---------------------------------------------------------------------------------
Update S_list_of_colors set List_Of_Colors = Replace(List_Of_Colors,'<','<')
Update S_list_of_colors set List_Of_Colors = Replace(List_Of_Colors,'>','>')
Update S_list_of_colors set List_Of_Colors = Replace(List_Of_Colors,'
','<EOL>') -- Mark End Of Line.
Update S_list_of_colors set List_Of_Colors = Replace(List_Of_Colors,'&','&')
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
-- Create the second level of concatenation.
--
--
-- Lists of lists
--
exec sp_drop S_list_of_colors_X
-- DROP TABLE IF EXISTS S_list_of_colors_X -- Use this in resent SQLserver versions. SP_drop performs the same function in my systems.
DECLARE @NL VARCHAR(30) = char(13)+char(10)
SELECT number,
STUFF((SELECT
', ' + letter+' = ('+List_Of_Colors+')'+@nl AS [text()]
FROM s_list_of_colors C
WHERE
C.number = T.number order by number
FOR XML PATH('') -- Select it as XML
), 1, 1, '' )
AS [List_Of_Colors]
INTO S_list_of_colors_X
FROM s_list_of_colors T group by number order by number
---------------------------------------------------------------------------------
Update S_list_of_colors_X set List_Of_Colors = Replace(List_Of_Colors,'<','<')
Update S_list_of_colors_X set List_Of_Colors = Replace(List_Of_Colors,'>','>')
Update S_list_of_colors_X set List_Of_Colors = Replace(List_Of_Colors,'
','<EOL>') -- Mark End Of Line.
Update S_list_of_colors_X set List_Of_Colors = Replace(List_Of_Colors,'&','&')
---------------------------------------------------------------------------------
--
-- Convert from nvarchar to varchar
--
exec sp_drop S_list_of_colors2
CREATE TABLE S_list_of_colors2(number int,letter varchar(3),list_of_colors varchar(max))
insert into S_list_of_colors2 select * from S_list_of_colors
select * from S_list_of_colors2
select *, DATALENGTH(list_of_colors) datalengte from s_list_of_colors2 order by datalengte desc
select * from S_colors
select * from S_list_of_colors
select * from S_list_of_colors_X
-- exec sp_generate_inserts s_colors
-- exec sp_generate_inserts s_list_of_colors
-- exec sp_generate_inserts s_list_of_colors_X
--
-- Drew.Allen
-- https://www.sqlservercentral.com/Forums/Topic2003216.aspx?Update=1
--
; WITH CTE AS
(
SELECT number, letter + ' = (' AS letter, ', (' + CAST(RIJ_NUMMER AS VARCHAR(15)) + ' = ' + color + ')' AS word, ')<EOL>' AS close_paren
, ROW_NUMBER() OVER(ORDER BY number, letter, RIJ_NUMMER, color) AS rn
, DENSE_RANK() OVER(ORDER BY number, letter) AS dr
FROM S_colors
)
SELECT
(
SELECT
CASE
WHEN GROUPING(letter) = 0 THEN NULL ELSE number END,
CHAR(10),
CASE WHEN GROUPING(close_paren) = 0 THEN NULL ELSE letter END,
CASE WHEN MIN(rn) = 1 THEN STUFF(word, 1, 2, '') ELSE word END,
close_paren + CHAR(10)
FROM CTE
GROUP BY GROUPING SETS((number), (number, letter), (word), (number, letter, close_paren))
ORDER BY MIN(dr), GROUPING(close_paren) DESC, GROUPING(number), GROUPING(letter) DESC, letter, GROUPING(word), MIN(rn)
FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(MAX)')
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply