0

I would like to take rows with all but one column containing duplicate data and take the one unique field and put it in one row, with the one column being added to. I am creating a table from three different databases to create:

Wk  RunOrder   Job Number    ModelNo ....   ItemTag
23     28          1234        abcd            1
23     28          1234        abcd            2
23     28          1234        abcd            3
23     28          1234        abcd            4
12     28          1222        abbd            2
12     28          1222        abbd            4

What I would Like is:

Wk  RunOrder   Job Number    ModelNo ....   ItemTag
23     28          1234        abcd          1,2,3,4
23     28          1222        abbd            2,4

the RunOrder and ItemTag columns are from different databases

NotDareDevil
  • 1
  • 2
  • Those links work with Apache.Spark, I am using sql-server managment, so the collect_list command doesn't seem to be a viable option, thanks for the link tho – NotDareDevil Jun 06 '19 at 18:17
  • Sorry, wrong link, it should be https://stackoverflow.com/questions/8005846/sql-server-combining-multiple-rows-into-one-row – Piotr Palka Jun 06 '19 at 18:20
  • No worries, that link seems to be a hardcoded solution, is there anyway to make it dynamic? Like I have more data in the list with different JobNumbers that I need to do the same thing to – NotDareDevil Jun 06 '19 at 18:29

1 Answers1

0

I was able to get the desired output below using a subquery. I assumed you wanted to keep Wk as part of the grouping. 

if OBJECT_ID('tempdb..#temp') is not null
    drop table #temp

CREATE TABLE #temp (
    Wk int,
    RunOrder int,
    JobNumber int,
    ModelNo VARCHAR(10),
    ItemTag VARCHAR(5)
)
insert into #temp  values(23,28,1234,'abcd',1)
insert into #temp  values(12,28,1234,'abcd',2)
insert into #temp  values(23,28,1234,'abcd',3)
insert into #temp  values(12,28,1234,'abcd',4)


select distinct  Wk,RunOrder,JobNumber,ModelNo,
    SUBSTRING(
        (
            SELECT ','+t1.ItemTag  AS [text()]
            FROM #temp t1
            WHERE t1.Wk = t2.Wk and t1.RunOrder=t2.RunOrder and t1.JobNumber=t2.JobNumber and t1.ModelNo=t2.ModelNo
            ORDER BY t1.Wk
            FOR XML PATH ('')
        ), 2, 1000) t
from #temp t2

Another solution you could do is use a derived table.

reidh.olsen
  • 111
  • 3
  • I Might have to do a sub-query then. I am creating the above results from three different tables, Sorry I did not mention that, I will throw that in – NotDareDevil Jun 06 '19 at 18:20
  • Sorry I wasn't very clear, the above solution uses a 'subquery'. I don't know the schema of the other tables, but one solution could be to join them all and put them into a temp table, then use my solution. – reidh.olsen Jun 06 '19 at 18:49
  • You should also be able to replace the #temp with the joining of your tables, depending on the schemas. – reidh.olsen Jun 06 '19 at 18:50
  • I was thinking of using a temp table. I also forgot to mention i need the program to work dynamically, since I have other repeated values but of varying degree. Thank you for you help and time tho, anything else is greatly appreciated – NotDareDevil Jun 06 '19 at 18:52
  • It should still work with different values, as long as you filter on the columns you want to 'group by' in the 'where' clause of the subquery. – reidh.olsen Jun 06 '19 at 18:54
  • I'm not saying it won't work with different values, I just notice you hardcoded those values in there, but I have a massive size database that has stuff added to it daily. I'm asking if there is a way to do this so I do not have to do it manually for each job – NotDareDevil Jun 06 '19 at 19:03
  • You can ignore the create and insert portion of my solution, that is only to recreate a similar environment to yours. – reidh.olsen Jun 06 '19 at 19:46