Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Help

Author

Topic

aswindba1
Yak Posting Veteran

62 Posts

Posted - 2013-12-12 : 19:47:51

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)

17689 Posts

Posted - 2013-12-12 : 20:12:27

[code]SELECT ServerName , MAX(sybase) , MAX(MQ) , MAX(Citrixfarm) , MAX(oracle) , MAX(UDBW) , MAX(IHS) , MAX(WAS)
FROM Server
GROUP BY ServerName[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-13 : 06:58:59

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

aswindba1
Yak Posting Veteran

62 Posts

Posted - 2013-12-13 : 12:00:54

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources