SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aswindba1
Yak Posting Veteran

USA
62 Posts

Posted - 12/12/2013 :  19:47:51  Show Profile  Reply with Quote
Hi



I ahve table called Server with different versions. I have duplicate rows because of Verisons. So i want to change my table structure to single row with all versions.

I have of thousandsof rwos like this.




I have values like below

| ServerName | sybase | MQ || Citrixfarm | oracle | UDBW || IHS || WAS |
----------------------------------------------------------------------------------------------------------------------
| ABC1234 | 6.2.5.0 || nULL || nULL || nULL|| || 6.2.5.0 || nULL | 2.7.6.3

| ABC1234 | nULL || 3.8.88.9 || nULL || 5.6.7.8 || NULL || nULL | NULL


i NEED THE OUPPUT LKE BELOW.


| ServerName | sybase | MQ || Citrixfarm | oracle | UDBW || IHS || WAS |
----------------------------------------------------------------------------------------------------------------------
| ABC1234 | 6.2.5.0 || 3.8.88.9 || nULL || 5.6.7.8 || 6.2.5.0 || nULL || 2.7.6.3

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 12/12/2013 :  20:12:27  Show Profile  Reply with Quote
SELECT ServerName , MAX(sybase) , MAX(MQ) , MAX(Citrixfarm) , MAX(oracle) , MAX(UDBW) , MAX(IHS) , MAX(WAS)
FROM   Server
GROUP BY ServerName



KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/13/2013 :  06:58:59  Show Profile  Reply with Quote
can there be a case wher you'll have multiple rows with non null values for any of the columns for a server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

aswindba1
Yak Posting Veteran

USA
62 Posts

Posted - 12/13/2013 :  12:00:54  Show Profile  Reply with Quote
hi thank you very much for your inputs. Ireally appreciate your response.

Finaly i get rid of this mess by using PIVOT table in slq server as below.

select ServerName,
SYBASE, MQ, [CITRIX FARM],
Oracle, [UDB DW],CITRIX, IHS, WAS, IIS, UDB,[.NET],[SQL SERVER]
from
(
SELECT
ServerName,
Adder_Name,
Adder_ver
FROM tableA

) d
pivot
(
Max(Adder_ver)
for Adder_Name in (SYBASE, MQ, [CITRIX FARM],
Oracle, [UDB DW],CITRIX, IHS, WAS, IIS, UDB,[.NET],[SQL SERVER])
) piv
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000