August 5, 2019 at 1:45 pm
Suppose I want to do a count, grouped by an article and some columns I do not want to group on but I want some examples of the content. Is there a 'simple' solution for this. (Not using FOR XML)
Example result.
Counted Article Colors Specials
2 Bike Green, Brown Handbrakes
34 Cups Yellow,green,brown,blanc etc. etc. Hollow, metal, plastic, footed, hollow and metal etc.
For the counted the count(*) group by the article and from the colors and specials a list of a few (different) examples.
Thanks for your time and attention.
Ben
In general this construction will be used for maintenance tasks where the article represents a number of columns which is important to the receiver of the information, the count is indicadictive, and the colours is some extra information to give some context.
August 5, 2019 at 3:26 pm
I don't think so. I couldn't get the concatenation of Colors to work without FOR XML.
SELECT SUM(Qty) AS 'Counted'
, ColorGrp.Article
, ColorGrp.Colors
FROM
(SELECT Article
, STUFF((SELECT ', ' + Color
FROM Things t1
WHERE t1.Article = t2.Article
ORDER BY Color ASC
FOR XML PATH('')),1,1,'') AS Colors
, Qty
FROM Things t2) ColorGrp
GROUP BY ColorGrp.Article
, ColorGrp.Colors;
August 5, 2019 at 3:42 pm
Agree with Pietlinden - only thing to add if you are in SQL Server 2017 you may use String_Agg function
Thanks
August 5, 2019 at 3:55 pm
Totally forgot about STRING_AGG()
SELECT SUM(Qty) AS Counted
, Article
, STRING_AGG(Color,', ') AS Colors
FROM Things
GROUP BY Article;
August 5, 2019 at 4:41 pm
keep in mind that neither solution will deduplicate the value list. So you will end up with a string listing 'red" 100 times, if 100 rows within a grouping point to 'red' as the color.
Realistically you will need to have a subquery to come up with a "distinct" list first then concatenate..
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 5, 2019 at 8:06 pm
> Suppose I want to do a count, grouped by an article and some columns I do not want to group on but I want some examples of the content. Is there a 'simple' solution for this. (Not using FOR XML)
Example result.
Counted Article Colors Specials
2 Bike Green, Brown Handbrakes
34 Cups Yellow, green, brown, blanc, etc.
Hollow, metal, plastic, footed, hollow and metal
For the counted the count(*) group by the article and from the colors and specials a list of a few (different) examples.<<
I hope you know that what you posted is a picture without any DDL and what it seems to model isn't even a table! Would you like to try to follow forum rules and actually post DDL? A column named "counted" is not an attribute – that's a verb form. We don't even know the name of the table in your picture.
You want to be using some tool other than SQL for this.
Please post DDL and follow ANSI/ISO standards when asking for help.
August 5, 2019 at 9:26 pm
Hello jcelko212 32090 and others,
You want to be using some tool other than SQL for this.
Luckily it's me who determines what I want, it's perfectly ok that you want to use other than SQL tools for similar kind of problems, but I like to use SQL to solve this kind of problems and all kinds of problems.
I hope you know that what you posted is a picture
I do post some pictures on line, never do I include a DDL with a picture. In general pictures are taken with camera's, they might include Exif information, but do rarely include DDL.
The question was answered by a number of people, so with the question I got the information I wanted, had I posted extensive DDL, I might not have received an answer, with this open question some people were nice enough to work out what I wanted and responded to that. My thanks for that.
The example was a result of a query, (to my knowledge that is always presented in a table form, I do not know if it constitutes a table), the word counted was a translation from my native language and I could have use the word COUNT, NUMBER or a more extensive name for this 'field/column/result' but the words COUNT, NUMBER and some other words are often avoided as an object name in SQL, because it can cause confusion. Sorry if I confused you with the wordt 'COUNTED'.
To the others and Joe, thanks for your anwsers, that this is not simple and/or not possible without FOR XML (or SQL-server 2017) is the most valuable answer, this prevents me from searching for an simple answer which can not be found.
To all who have contributed to this thread, thanks for your time and attention.
Ben
August 6, 2019 at 8:17 am
The hope was for a simple solution, without the FOR XML construction. Also I would like to avoid the CTE with a 'recursion' construction. Only 'a list of a few (different) examples' requested.
After a nights rest I came up with the following construction. (Not totaly elegant, but workable).
Example data:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'Example1') DROP TABLE Example1
select CONVERT(varchar(300),'fiets') article, CONVERT(varchar(300),'red') color, 1 druepel into Example1
insert into Example1 values('fiets','bleu',2)
insert into Example1 values('fiets','green',3)
insert into Example1 values('cakeform','brown',6)
insert into Example1 values('cakeform','black',6)
insert into Example1 values('mouse','grey',23)
insert into Example1 values('mouse','darkgrey',24)
insert into Example1 values('mouse','verygrey',7)
insert into Example1 values('mouse','white',2)
insert into Example1 values('mouse','white',1)
And the solution I came up with:
;
With
D1 as (SELECT DISTINCT COUNT(*) OVER (PARTITION BY Example1.ARTICLE) COUNTED, article, color FROM Example1)
, D2 as (SELECT DISTINCT article, CONVERT(varchar(8),druepel) druepel FROM Example1)
, E1 as (SELECT *, row_number() over (partition by article order by color) color_rn FROM D1)
, E2 as (SELECT *, row_number() over (partition by article order by druepel) druepel_rn FROM D2)
, L1 as (
select distinct R1.*, R1.COLOR+coalesce(', '+R2.COLOR,'')+coalesce(', '+R3.COLOR,'')+coalesce(', '+R4.COLOR,'') LIST1
from E1 R1
left join E1 R2 on R1.article = R2.article AND R1.color_rn+1 = R2.color_rn and r1.color_rn = 1
left join E1 R3 on R2.article = R3.article AND R2.color_rn+1 = R3.color_rn
left join E1 R4 on R3.article = R4.article AND R3.color_rn+1 = R4.color_rn
where r1.color_rn = 1)
, L2 as (
select distinct R1.*, R1.druepel+coalesce(', '+R2.druepel,'')+coalesce(', '+R3.druepel,'')+coalesce(', '+R4.druepel,'') LIST2
from E2 R1
left join E2 R2 on R1.article = R2.article AND R1.druepel_rn+1 = R2.druepel_rn and r1.druepel_rn = 1
left join E2 R3 on R2.article = R3.article AND R2.druepel_rn+1 = R3.druepel_rn
left join E2 R4 on R3.article = R4.article AND R3.druepel_rn+1 = R4.druepel_rn
where r1.druepel_rn = 1)
select
COUNTED,
L1.ARTICLE,List1,list2 from L1 join L2 on L1.article = L2.article
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'Example1') DROP TABLE Example1
This gives as a result:
COUNTED ARTICLE List1 list2
2 cakeform black, brown 6
3 fiets bleu, green, red 1, 2, 3
5 mouse darkgrey, grey, verygrey, white 1, 2, 23, 24
(Sorry for the formatting).
The limitations:
The lists is limited to a 'fixed' number of elements (here 4, for both lists).
The frequency of the elements do not affect which elements are present in the list (with an extra layer this can be amended).
A JOIN construction of several elements for 'each list' is needed (???) / used.
So it is not totaly elegant. But the code can be 'generalised' by using 'generalised' names for the fields which should be listed.
With some fairly simple copy/pasting the number of elementes in a list can be extended.
With some fairly simple copy/pasting the number of lists can be extended.
Thans for all your participation,
Ben
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply