SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


query Help


query Help

Author
Message
DBA12345
DBA12345
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 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
pietlinden
pietlinden
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14008 Visits: 14127
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.
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17715 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
laurie-789651
laurie-789651
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1440 Visits: 1272
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



Ed Wagner
Ed Wagner
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48559 Visits: 10844
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
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41246 Visits: 20000
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
DBA12345
DBA12345
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search