Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

query Help Expand / Collapse
Author
Message
Posted Thursday, December 12, 2013 5:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 24, 2014 8:19 PM
Points: 56, Visits: 240
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
Post #1522538
Posted Thursday, December 12, 2013 11:02 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 700, Visits: 4,481
Your table is really structured like that? What happens if you get more software packages? Does the table structure change? IF y

I think storing information using this structure/pattern is bound to fail. I worked on data like this every day for six months, and it was a nightmare.

Why not use a different table design?

Server===(1,M)---InstalledSoftware---(M,1)---SoftwareVersion---(M,1)---SoftwareTitle

then you can add servers, versions, titles all you want. If you want a crosstab, use SSRS or do a PIVOT.
Post #1522573
Posted Friday, December 13, 2013 12:12 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:12 PM
Points: 3,609, Visits: 5,220
pietlinden (12/12/2013)
Your table is really structured like that? What happens if you get more software packages? Does the table structure change? IF y

I think storing information using this structure/pattern is bound to fail. I worked on data like this every day for six months, and it was a nightmare.

Why not use a different table design?

Server===(1,M)---InstalledSoftware---(M,1)---SoftwareVersion---(M,1)---SoftwareTitle

then you can add servers, versions, titles all you want. If you want a crosstab, use SSRS or do a PIVOT.


+1 +1 +1



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1522587
Posted Friday, December 13, 2013 4:45 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
This would work for the data you've given:


USE [tempdb]

--== SAMPLE DATA ==--

IF OBJECT_ID('tempdb..#Servers') IS NOT NULL DROP TABLE #Servers

CREATE TABLE #Servers
(
ServerName Varchar(25) NULL,
sybase Varchar(25) NULL,
MQ Varchar(25) NULL,
Citrixfarm Varchar(25) NULL,
oracle Varchar(25) NULL,
UDBW Varchar(25) NULL,
IHS Varchar(25) NULL,
WAS Varchar(25) NULL
)

INSERT #Servers
(ServerName, sybase, MQ, Citrixfarm, oracle, UDBW, IHS, WAS)
VALUES
('ABC1234', '6.2.5.0', NULL, NULL, NULL, '6.2.5.0', NULL, '2.7.6.3')

INSERT #Servers
(ServerName, sybase, MQ, Citrixfarm, oracle, UDBW, IHS, WAS)
VALUES
('ABC1234', NULL, '3.8.88.9', NULL, '5.6.7.8', NULL, NULL, NULL)

SELECT * FROM #Servers

--== SUGGESTED SOLUTION ==--

SELECT ServerName,
sybase = MAX(sybase),
MQ = MAX(MQ),
Citrixfarm = MAX(Citrixfarm),
oracle = MAX(oracle),
UDBW = MAX(UDBW),
IHS = MAX(IHS),
WAS = MAX(WAS)
FROM #Servers
GROUP BY ServerName
ORDER BY ServerName


Post #1522633
Posted Friday, December 13, 2013 6:16 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:05 PM
Points: 3,943, Visits: 2,957
dwain.c (12/13/2013)
pietlinden (12/12/2013)
Your table is really structured like that? What happens if you get more software packages? Does the table structure change? IF y

I think storing information using this structure/pattern is bound to fail. I worked on data like this every day for six months, and it was a nightmare.

Why not use a different table design?

Server===(1,M)---InstalledSoftware---(M,1)---SoftwareVersion---(M,1)---SoftwareTitle

then you can add servers, versions, titles all you want. If you want a crosstab, use SSRS or do a PIVOT.


+1 +1 +1

+1 also. In the original design, you'd need to keep adding columns forever. Properly structuring it will help you so very much in the end. The pain you'll avoid down the line by restructuring it now will be very much worth the effort.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1522651
Posted Friday, December 13, 2013 6:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 7,115, Visits: 13,476
pietlinden (12/12/2013)
Your table is really structured like that? What happens if you get more software packages? Does the table structure change? IF y

I think storing information using this structure/pattern is bound to fail. I worked on data like this every day for six months, and it was a nightmare.

Why not use a different table design?

Server===(1,M)---InstalledSoftware---(M,1)---SoftwareVersion---(M,1)---SoftwareTitle

then you can add servers, versions, titles all you want. If you want a crosstab, use SSRS or do a PIVOT.


+1. Normalise this mess.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1522657
Posted Friday, December 13, 2013 10:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 24, 2014 8:19 PM
Points: 56, Visits: 240
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
Post #1522779
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse