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.
| Author |
Topic |
|
svebbi
Starting Member
2 Posts |
 Posted - 2012-07-31 : 08:32:56
|
| Hi everyone!In my company we have build a dashboard that monitors diskdrives at different customers. As it is now we monitor all drives on the servers. But we want to only monitor the ones that are used for BI. Therefore, I have created a table with the BI-drives. Now the challenge is I want to be able to only have the drives shown once... Let me explain with the following example:Below is a table with drive letters.C C <----- output should be: CY Z <----- output should be: Y, ZD L <----- output should be: D, LE HF GK LD D <----- output should be: D... and so forth.I think the trick is to be to sort on row level. My first hunch is to make CASE IF statement where I check if the following column is similar to the latter. But since I have a series of drive letters (more columns than the two I showed in my example) it would a lot of CASE IFs. I don't believe that is the best solution.An other way I have been thinking of is to pivot the table and then check vertically but that seems a little over the top.Does anyone know how to handle this on a row level?Thank you in advance. :)www.inspari.dk |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-07-31 : 09:20:45
|
SELECT Col1, NULLIF(Col2, Col1) AS Col2 FROM dbo.Table1SELECT Col1 + CASE WHEN Col2 = Col1 THEN '' ELSE ', ' + Col2 END AS Col2 FROM dbo.Table1
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2012-07-31 : 09:24:36
|
| if you've got 26 pairs of columns then you're stuffed and you have to do it Peso says. If you can change your query to rows then you can most likely pair them up using ROW_NUMBER partitioned by the letter to get letter 1 & letter 2. Then you can join up rows 1 & 2 for each partition then PIVOT. But either way you need to do something 26 times or however many pairs you think you will have unless your front end can do something with it (SSRS can for example). |
|
|
|
|
|
|
Compare on row level