December 12, 2013 at 5:48 pm
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
December 12, 2013 at 11:02 pm
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.
December 13, 2013 at 12:12 am
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 yI 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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 13, 2013 at 4:45 am
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
(
ServerNameVarchar(25) NULL,
sybaseVarchar(25) NULL,
MQVarchar(25) NULL,
CitrixfarmVarchar(25) NULL,
oracleVarchar(25) NULL,
UDBWVarchar(25) NULL,
IHSVarchar(25) NULL,
WASVarchar(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
December 13, 2013 at 6:16 am
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 yI 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.
December 13, 2013 at 6:27 am
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 yI 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.
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
December 13, 2013 at 10:00 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply