July 20, 2011 at 10:52 am
Hello All,
I have a table of staff members with a home, cell, and alternate phone numbers. Each phone number is assigned a priority of 1, 2, or 3.
I want to produce a result set that will present the phone numbers based on that priority as well as a description of what kind of number each one is.
I believe the sample data and desired results below should adequately explain the problem.
Thanks to any one who can help.
CREATE TABLE Staff
(
StaffID INT,
Home [varchar](10) NULL,
HomePriority [int] NOT NULL,
Cell [varchar](10) NULL,
CellPriority [int] NOT NULL,
Alternate [varchar](10) NULL,
AlternatePriority [int] NOT NULL
)
INSERT INTO Staff
SELECT 1, '1111234561',1,'2222345672',2,'3339876543',3 UNION
SELECT 2, '2221234523',2,'3332345665',3,'1119876597',1 UNION
SELECT 3, '3331234511',3,'2222345625',2,'1119876587',1 UNION
SELECT 4, '2221234531',2,'1112345635',1,'3339876576',3
--Desired Results
--StaffID,Phone1,Phone1Type,Phone2,Phone2Type,Phone3,Phone3Type
--1,1111234561,Home,2222345672,Cell,3339876543,Alternate
--2,1119876597,Alternate,2221234523,Home,3332345665,Cell
--3,1119876587,Alternate,2222345625,Cell,3331234511.Home
--4,1112345635,Cell,2221234531,Home,3339876576,Alternate
SELECT * FROM Staff
July 20, 2011 at 12:48 pm
You get an A+ for setting up the problem so perfectly. Thank you so much!! 😀
Here's a solution for you
select StaffID
,COALESCE(case when homepriority = 1 then home else null end
,case when cellpriority = 1 then cell else null end
,case when alternatepriority = 1 then alternate else null end
) as phone1
,COALESCE(case when homepriority = 1 then 'Home' else null end
,case when cellpriority = 1 then 'Cell' else null end
,case when alternatepriority = 1 then 'Alternate' else null end
) as phone1type
,COALESCE(case when homepriority = 2 then home else null end
,case when cellpriority = 2 then cell else null end
,case when alternatepriority = 2 then alternate else null end
) as phone2
,COALESCE(case when homepriority = 2 then 'Home' else null end
,case when cellpriority = 2 then 'Cell' else null end
,case when alternatepriority = 2 then 'Alternate' else null end
) as phone2type
,COALESCE(case when homepriority = 3 then home else null end
,case when cellpriority = 3 then cell else null end
,case when alternatepriority = 3 then alternate else null end
) as phone3
,COALESCE(case when homepriority = 3 then 'Home' else null end
,case when cellpriority = 3 then 'Cell' else null end
,case when alternatepriority = 3 then 'Alternate' else null end
) as phone3type
from Staff
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 21, 2011 at 6:14 am
We cannot always alter the existing schema. However, we can mimic 1NF with code!
This is a simplified query
SELECTs.StaffID,
MAX(CASE WHEN f.Priority = 1 THEN f.Number ELSE '' END) AS Phone1,
MAX(CASE WHEN f.Priority = 1 THEN f.Type ELSE '' END ) AS Phone1Type,
MAX(CASE WHEN f.Priority = 2 THEN f.Number ELSE '' END ) AS Phone2,
MAX(CASE WHEN f.Priority = 2 THEN f.Type ELSE '' END ) AS Phone2Type,
MAX(CASE WHEN f.Priority = 3 THEN f.Number ELSE '' END ) AS Phone3,
MAX(CASE WHEN f.Priority = 3 THEN f.Type ELSE '' END ) AS Phone3Type
FROMdbo.Staff AS s
CROSS APPLY(
VALUES('Home', HomePriority, Home),
('Cell', CellPriority, Cell),
('Alternate', AlternatePriority, Alternate)
) AS f(Type, Priority, Number)
GROUP BYs.StaffID
N 56°04'39.16"
E 12°55'05.25"
July 21, 2011 at 8:20 am
CELKO (7/21/2011)
>> We cannot always alter the existing schema. <<I have started asking if this is possible in my standard "cut & paste" asking for minimal Netiquette. My experience for the last few decades is that bad DDL like this is the source of 80% or more of the DML problems and more than 90% of the performance problems when the system grows.
I absolutely agree with THAT! Bad design not only causes performance problems, but it limits flexibility (can't add a "work" phone or 2nd alternate to the current design without a table change).
The problem is that of 3rd party vendors (or, sometimes the "enemy" is within), many of which should be driven out of business for their simple ineptitude when it comes to decent table design. Yes, I agree... people should review their design before buying their product, but we all know how that works out.
So, yeah... good question to ask in a standard cut'n'paste reply header.
--Jeff Moden
July 21, 2011 at 8:32 am
My schema design is not vendor-provided, legacy, already-in-production, it simply is ...bad design.
Joe: thank you for pointing this out. I am now fixing the design.
July 21, 2011 at 9:13 am
Chrissy,
Please note that the query using the revised design provides output in this format
Staff_ID Phone_nbr
1 1111234561
2 1119876597
3 1119876587
4 1112345635
Edited to add:
Here is a query to get the desired results you requested from the new table design.
SELECT s.Staff_ID,
MAX(CASE WHEN s.phone_priority = 1 THEN s.phone_nbr ELSE '' END) AS Phone1,
MAX(CASE WHEN s.phone_priority= 1 THEN s.phone_type ELSE '' END ) AS Phone1Type,
MAX(CASE WHEN s.phone_priority = 2 THEN s.phone_nbr ELSE '' END ) AS Phone2,
MAX(CASE WHEN s.phone_priority = 2 THEN s.phone_type ELSE '' END ) AS Phone2Type,
MAX(CASE WHEN s.phone_priority = 3 THEN s.phone_nbr ELSE '' END ) AS Phone3,
MAX(CASE WHEN s.phone_priority = 3 THEN s.phone_type ELSE '' END ) AS Phone3Type
FROM Staff_Phones s
group by staff_ID
order by staff_ID
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 24, 2011 at 6:15 pm
Thank you. My database structure has been fixed and the additional code you provided is working. The non-normalized structure I need to output to is necessary to satisfy the requirement of a third party.
July 24, 2011 at 9:48 pm
You can always create a denormalised view to satisfy the third-party application.
But always keep the data normalised in the database.
There is a big differense between storing the data, and enabling the data.
N 56°04'39.16"
E 12°55'05.25"
July 26, 2011 at 7:52 am
"You can always create a denormalised view to satisfy the third-party application.
But always keep the data normalised in the database."
"There is a big differense between storing the data, and enabling the data."
Very well stated. This is a concept that is rarely understood and thus feared by far too many people with design decision making powers they should not have.
The probability of survival is inversely proportional to the angle of arrival.
July 26, 2011 at 7:56 am
Thank you.
On the other hand, if all people did this I would be out of work 🙂
N 56°04'39.16"
E 12°55'05.25"
December 30, 2011 at 10:55 am
" Select StaffID,'Phone1'=
(case when HomePriority=1 then home
when CellPriority=1 then Cell
when AlternatePriority=1 then Alternate else null end),
'Phone1Type'=(case when HomePriority=1 then 'home'
when CellPriority=1 then 'cell'
when AlternatePriority=1 then 'Alternate' else null end),
'Phone2'= (case when HomePriority=2 then home
when CellPriority=2 then Cell
when AlternatePriority=2 then Alternate else null end),
'Phone2Type'= (case when HomePriority=2 then 'home'
when CellPriority=2 then 'cell'
when AlternatePriority=2 then 'Alternate' else null end),
'Phone3'=(case when HomePriority=3 then home
when CellPriority=3 then cell
when AlternatePriority=3 then Alternate else null end),
'Phone3Type'=(case when HomePriority=3 then 'home'
when CellPriority=3 then 'cell'
when AlternatePriority=3 then 'Alternate' else null end)
from Staff "
sorry for posting a reply for such an old thread, I am sql server newbie recently starting to learn the ropes of Sql querying.I have found working on all the questions in the forums and old threads helpful and have been posting solutions on the threads.If anyone thinks i need to have a better querying approach,please feel free to correct me.:-)
December 31, 2011 at 8:58 am
manoj0134 (12/30/2011)
sorry for posting a reply for such an old thread, I am sql server newbie recently starting to learn the ropes of Sql querying.I have found working on all the questions in the forums and old threads helpful and have been posting solutions on the threads.If anyone thinks i need to have a better querying approach,please feel free to correct me.:-)
Absolutely nothing wrong with that! It's a time honored way of learning especially when someone takes the time to reply with suggestions! Welcome aboard!
Just a couple of quick notes...
First, nicely done on handling the denormalized data. Although the code can sometimes be a bit longer, they're frequently much faster than using a classic GROUP BY style of Cross Tabbing. The underlying problem with all of that is that usually means the table isn't properly normalized and won't suffer changes in requirements very easily.
Shifting gears, I also use the columnname = expression method... it makes for some pretty easy to read code. However (and I'd have to look for the MS document), the 'columnname' = expression method (notice the single quotes there, the use of single quotes is what has been deprecated) has been deprecated and should be avoided for new code and, perhaps, be repaired whenever encounted in old code when a modification is required for other reasons. Of course, spaces and other characters that require quoted identifiers should be generally avoided but, if such characters are necessary, then you should encapsulate the column name in brackets rather than single quotes.
Last but not least, consider a strong "vertical alignment" convention to make the code even more easy to read. Using your "casing" convention (my convention is quite different), here's how I would write the code...
Select StaffID,
Phone1 =
case
when HomePriority = 1 then Home
when CellPriority = 1 then Cell
when AlternatePriority = 1 then Alternate
else null
end,
Phone1Type =
case
when HomePriority = 1 then 'Home'
when CellPriority = 1 then 'Cell'
when AlternatePriority = 1 then 'Alternate'
else null
end,
Phone2 =
case
when HomePriority = 2 then Home
when CellPriority = 2 then Cell
when AlternatePriority = 2 then Alternate
else null
end,
Phone2Type =
case
when HomePriority = 2 then 'Home'
when CellPriority = 2 then 'Cell'
when AlternatePriority = 2 then 'Alternate'
else null
end,
Phone3 =
case
when HomePriority = 3 then Home
when CellPriority = 3 then Cell
when AlternatePriority = 3 then Alternate
else null
end,
Phone3Type =
case
when HomePriority = 3 then 'Home'
when CellPriority = 3 then 'Cell'
when AlternatePriority = 3 then 'Alternate'
else null
end,
from dbo.Staff
;
Such "vertical alignment" makes it very easy to see that each pair of columns has been handled in an identical fashion which would also make it super easy for someone to add another column should the need ever arise.
I also use the 2 part naming convention for all tables except Temp Tables to try to get folks into the habit of that "best practice".
To include code on a post in one of those nice little colorized windows, simply include the code inbetween the appropriate "IFCode Shortcuts" that you will see to the left of the edit window when you're building your post. I recommend converting TABs to spaces to make you're life a bit easier as what you paste into the shortcut will be pretty much duplicated from what you see in SSMS (although it's not perfect).
Last but not least, the "shape" of this post changed as it progressed. It was pointed out that the original form of the table was denormalized and should probably be normalized, instead. The next logical step would be for you to change the example table to a normalized form and then write code against that (or use the example code already posted).
--Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply