merge data in SQL with data in columns

Question

i have temporary table with data

    count1  serverinfo  csdversion  extend  servicepackdate piecolor
26  Microsoft Windows 8.1 Entreprise        NULL    2023-07-11  green
1   Microsoft® Windows Vista™ Enterprise        NULL    2010-04-13  black
2   Microsoft® Windows Vista™ Entreprise        NULL    2010-04-13  black
31698   Microsoft Windows 7 Enterprise  Service Pack 1  NULL    2020-04-13  green
9918    Microsoft Windows 7 Entreprise  Service Pack 1  NULL    2020-04-13  green

please find snapshot

i need to merge Microsoft windows 7 enterprise & Microsoft windows 7 entreprise can you please tell me how

main query from where data is coming from SCCM tables

    declare @variable varchar
create table #temptable(count1 int,serverinfo varchar(max),csdversion varchar(max),extend date,servicepackdate date,piecolor varchar(max))
insert into #temptable(count1,serverinfo,csdversion,extend,servicepackdate,piecolor)
select count(distinct OS.resourceid) as ModelCount,serverinfo,dt.Servicepack,dt.extendedsupportEnddate,dt.servicepacksupportenddate,
case
when DATEDIFF(month,getdate(), dt.servicepacksupportenddate) < 0 then 'black'
when DATEDIFF(month,getdate(),dt.servicepacksupportenddate) > 0 and DATEDIFF(month, getdate(),dt.servicepacksupportenddate) < 6  then 'red'
when DATEDIFF(month,getdate(),dt.servicepacksupportenddate) > 6 and DATEDIFF(month, getdate(),dt.servicepacksupportenddate) < 12  then 'orange'
when DATEDIFF(month,getdate(),dt.servicepacksupportenddate) > 12  then 'green'
ELSE ''
End As PieColor 
 from [dbo].[v_GS_OPERATING_SYSTEM] OS
inner join datesinfo dt on  os.caption0=dt.serverinfo where os.caption0 not like '%Server%' and  os.caption0 not like '%Windows 10%'
group by dt.Servicepack,serverinfo,dt.servicepacksupportenddate,dt.extendedsupportenddate



select * from #temptable

data in sccm tables is wrong so to correct that we have to taken enterprise and entreprise as same

data in datesinfo table

serverinfo  LifecyclestartDate  ExtendedSupportEnddate  servicepacksupportenddate   servicepack
Microsoft Windows Server 2008 R2 Enterprise 2009-10-22  NULL    2020-01-14  Service Pack 1
Microsoft Windows Server 2008 R2 Standard   2009-10-22  NULL    2020-01-14  Service Pack 1
Microsoft® Windows Server® 2008 Standard    2008-05-06  NULL    2011-04-09  Service Pack 1
Microsoft® Windows Server® 2008 Enterprise  2008-05-06  NULL    2020-01-14  Service Pack 2
Microsoft® Windows Server® 2008 Standard    2008-05-06  NULL    2020-01-14  Service Pack 2
Microsoft Windows Server 2012 Standard  2012-10-30  NULL    2023-10-10  Service Pack 2
Microsoft Windows Server 2012 R2 Standard   2012-10-30  NULL    2023-10-10  Service Pack 2
Microsoft Windows Server 2012 Datacenter    2012-10-30  2018-10-09  2023-10-10  
Microsoft Windows Server 2012 R2 Datacenter 2012-10-30  2018-10-09  2023-10-10  
Microsoft Windows Server 2016 Standard  2016-10-15  2022-01-11  2027-01-12  
Microsoft Windows Server 2016 Datacenter    2016-10-15  2022-01-11  2027-01-12  
Microsoft® Windows Vista™ Enterprise    2007-01-25  NULL    2010-04-13  
Microsoft Windows 7 Enterprise  2007-01-25  NULL    2020-04-13  Service Pack 1
Microsoft Windows 8.1 Entreprise    2013-11-13  NULL    2023-07-11  
Microsoft® Windows Vista™ Entreprise    2007-01-25  NULL    2010-04-13  
Microsoft Windows 7 Entreprise  2007-01-25  NULL    2020-04-13  Service Pack 1
Microsoft Windows 8.1 Entreprise    2013-11-13  NULL    2023-07-11  
Microsoft Windows 10 Enterprise 1900-01-01  1900-01-01  2019-10-08  15063
Microsoft Windows 10 Entreprise 1900-01-01  1900-01-01  2019-10-08  15063
Microsoft Windows 10 Entreprise 1900-01-01  1900-01-01  2020-04-14  16299
Microsoft Windows 10 Entreprise 1900-01-01  1900-01-01  2020-11-10  17134
Microsoft Windows 10 Enterprise 1900-01-01  1900-01-01  2020-11-10  17134
Microsoft Windows 10 Enterprise 1900-01-01  1900-01-01  2021-05-10  17763
Microsoft Windows 10 Entreprise 1900-01-01  1900-01-01  2021-05-10  17763

What do you mean you need to "merge" them? What result do you want to get? — Tab Alleman, Mar 04 '19 at 14:31
https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server HTH — rach, Mar 04 '19 at 14:31
You should probably show us the query which generated that result set, assuming it is not just a simple `SELECT *`. — Tim Biegeleisen, Mar 04 '19 at 14:32
yes its query from where iam getting this result set i have pasted in questions — deepti, Mar 04 '19 at 14:37

score 0 · Answer 1 · answered Mar 04 '19 at 14:32

0

Take your query and GROUP BY serverinfo. Use aggregation functions in the rest of the columns: eg sum(count1) of you want all count1 to be summed, and max(csdversion) if you are SURE all cdsversions will be the same.

answered Mar 04 '19 at 14:32

George Menoutis

6,894
3
19
43

score 0 · Answer 2 · answered Mar 04 '19 at 14:33

0

Try a GROUP BY query, taking the sum of the count, and aggregating by all other columns:

SELECT
    SUM(count1) AS count1,
    serverinfo,
    csdversion,
    extend,
    servicepackdate,
    piecolor
FROM #temp_table
GROUP BY
    serverinfo,
    csdversion,
    extend,
    servicepackdate,
    piecolor;

answered Mar 04 '19 at 14:33

Tim Biegeleisen

502,043
27
286
360

Asking a question the way you did, leaving out critical information, usually won't work either. This is a good attempt, given that you left out the query. – Tim Biegeleisen Mar 04 '19 at 14:37

merge data in SQL with data in columns

2 Answers2