0

I have SQL table like this Table:1

CERT SPEC  ClientID
all   1    8
all   2    8
all   3    8
1    all   9
2    all   9

Table: 2 certs

ID  Name
1   A
2   B
3   C

Table: 3 SPECS

ID  Name
1   X
2   Y
3   Z

Result table

CERT SPEC   ClientID
all   X,Y,Z 8
A,B   all   9

I have use stuff and xml path for but did not get the expected result. Please help me with this.

abhi kumar
  • 35
  • 5
  • 1
    Don't do this; never store delimited data in your DBMS. But there are already 100's of examples on how to achieve it in SQL Server on SO. – Thom A Jul 01 '20 at 10:57
  • Don't do that - that breaks the most basic rule, 1st Normal Form - a cell holds one value. It's now extremely hard to find rows where `SPEC` contains `y` and almost impossible to add another relation – Panagiotis Kanavos Jul 01 '20 at 10:58
  • It looks like he want a `SELECT` statement, not really storing the data into table @Larnu – Ilyes Jul 01 '20 at 10:59
  • Result "Table" suggests to me a table, rather than a result/dataset @Sami . But regardless, the dupe tells them what to do if they really want to pursue a denormalised data set; both in more recent and older versions of SQL Server. At best, considering that they have a normalised design, it would be better if they use a `VIEW`, rather than "store" the data in a "result *table*". – Thom A Jul 01 '20 at 11:00
  • 1
    @Sami the title clearly says 'store' – P.Salmon Jul 01 '20 at 11:00
  • Fair enough Larnu. @P.Salmon I always try to learn the Q not just the title especially when the OP is a new user or maybe he has poor english. – Ilyes Jul 01 '20 at 11:02

0 Answers0