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 |
mattie
Starting Member
13 Posts |
Posted - 2013-02-02 : 11:52:18
|
When using Case Statements: Case When ( Turfs like '%Azalea%' or Turfs like '%Prichard%' or Turfs like '%Saraland%' ) then Tech_NAme End as SATGreer,Case When ( Turfs like '%Airprt%'then Tech_NAme End as SATSchillinger, Is there a way to change this to the "Desired Result" below?Tech NameSallyJane Suzy Mark Judy Desired Result: Sally MarkJane Judy Suzy |
|
mattie
Starting Member
13 Posts |
Posted - 2013-02-02 : 13:10:09
|
The first table should have posted like Sally, Jane and Suzy in the first column row 1,2,3. Mark Judy in Column 2 Row 4,5. Need column 2 to be in Row 1,2. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-02 : 15:52:53
|
I didn't completely follow your requirements or data, so there may be some other ways to do this more concisely and efficiently, but is something like this that you are looking for?SELECT SATGreer,SATSchillingerFROM( SELECT tech_name AS SATGreer, ROW_NUMBER() OVER (ORDER BY tech_name) AS N FROM Tbl WHERE Turfs like '%Azalea%' or Turfs like '%Prichard%' or Turfs like '%Saraland%')aFULL JOIN( SELECT tech_name AS SATSchillinger, ROW_NUMBER() OVER (ORDER BY tech_name) AS N FROM Tbl WHERE Turfs like '%Airprt%')b ON a.N = b.N |
|
|
mattie
Starting Member
13 Posts |
Posted - 2013-02-03 : 09:04:13
|
That looks like it may work. Would it be possbile to incorporate this into the the full Query? Or could you show an example format?The ultimate goal is to go ino columns on an Excel sheet, but with the numerous blanks in each column it's not feasible like it is. Thx! Here is the full query: Select MAX(Sched_Date) , datepart (weekday, SCHED_DATE) as Day_Week , TECH_NAME, --Mobile Regular Saturday Schedule Case When ( Turfs like '%Azalea%' or Turfs like '%Prichard%' or Turfs like '%Saraland%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =7 then Tech_NAme End as SATGreer,Case When ( Turfs like '%Airprt%' or Turfs like '%Semmes%' or Turfs like '%Springhill%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =7 then Tech_NAme End as SATSchillinger, Case When (Turfs Like'%Old_Shell%' or Turfs like '%Skyline%' or Turfs like '%Theodore%' or Turfs like '%bayfront%' or turfs like '%Bell_Fontaine%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =7 Then Tech_Name End as SatDemo, Case When (Turfs Like'%Bay_Minette%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =7 Then Tech_Name End as SatBayMinette, Case When (Turfs Like'%Brewton%' or Turfs like '%Flomaton%' or Turfs like '%Evergreen%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =7 Then Tech_Name End as SatBFE, Case When (Turfs Like'%Fairhope%' or Turfs like '%Spanish_FT%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =7 Then Tech_Name End as SatDaphne, Case When ( Turfs like '%Jackson_%' or Turfs like '%Thomasville%' ) and Turfs not like '%Jacksonville%' and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =7 then Tech_NAme End as SATJackson,Case When ( Turfs like '%Mt_Vernon_%' or Turfs like '%Citronelle%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =7 then Tech_NAme End as SATMtVernon,--Mobile Sunday Schedule Case When ( Turfs like '%Azalea%' or Turfs like '%Prichard%' or Turfs like '%Saraland%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =1 then Tech_NAme End as SunGreer,Case When ( Turfs like '%Airprt%' or Turfs like '%Semmes%' or Turfs like '%Springhill%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =1 then Tech_NAme End as SunSchillinger, Case When (Turfs Like'%Old_Shell%' or Turfs like '%Skyline%' or Turfs like '%Theodore%' or Turfs like '%bayfront%' or turfs like '%Bell_Fontaine%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =1 Then Tech_Name End as SunDemo, Case When (Turfs Like'%Bay_Minette%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =1 Then Tech_Name End as SunBayMinette, Case When (Turfs Like'%Brewton%' or Turfs like '%Flomaton%' or Turfs like '%Evergreen%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =1 Then Tech_Name End as SunBFE, Case When (Turfs Like'%Fairhope%' or Turfs like '%Spanish_FT%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =1 Then Tech_Name End as SunDaphne, Case When ( Turfs like '%Jackson_%' or Turfs like '%Thomasville%' ) and Turfs not like '%Jacksonville%' and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =1 then Tech_NAme End as SunJackson,Case When ( Turfs like '%Mt_Vernon_%' or Turfs like '%Citronelle%' ) and sch_profile not like 'Call%' and datepart(weekday, Sched_Date) =1 then Tech_NAme End as SunMtVernon,Case When datepart (weekday, SCHED_DATE) = 1 Then 'Sunday' When datepart (weekday, SCHED_DATE) = 7 Then 'Saturday' End as Weekend From TBLWHERE SCHED_DATE >=DATEADD(day,(DATEDIFF(day,0,GETDATE())/7) * 7,5)AND SCHED_DATE < DATEADD(day,(DATEDIFF(day,0,GETDATE())/7) * 7,7)And Group_ID like 'AL%' and turfs like '%PCA' and SCH_1 not like 'on 00:00-00:02' and (Sch_2 not like 'N%' and Sch_2 not like 'DP%' and Sch_2 not like 'I%' and Sch_2 not like 'HO%' and Sch_2 not like 'Vacation%' and SCH_2 not like 'off%' and sch_2 not like 'SO%' and sch_2 not like 'NH%' or sch_2 is null) GROUP BY DATEPART(weekday,SCHED_DATE), TECH_NAME, Turfs, Sch_Profile |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-03 : 18:37:37
|
If you have the flexibility to do so, add two columns to the table indicating which category each row belongs to (such as SunGreer, SunSchillinger etc.) and the ordering number. Then, you can do a simple select and PIVOT on the tech_name column. That will make the code simpler and more robust.If that gives you what you want, you may want to consider additional enhancements, such as normalizing it to pull out all the category names to a separate table. Also, you can choose not to have a id column as in my example below, instead calculate it on the fly. Give that a try and post back if that does not seem to work right.CREATE TABLE #tmp (id int, tech_name VARCHAR(32), category VARCHAR(32));INSERT INTO #tmp VALUES (1,'A','SunGreer'),(2,'B','SunGreer'),(3,'C','SunGreer'),(1,'D','SunSchillinger'),(2,'E','SunSchillinger');SELECT SunGreer,SunSchillingerFROM #tmp PIVOT ( MAX(tech_name) FOR category IN ([SunGreer],[SunSchillinger]))P DROP TABLE #tmp; |
|
|
mattie
Starting Member
13 Posts |
Posted - 2013-02-04 : 12:23:59
|
I have view only access to the tables, if you have any other idea. Thx. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-04 : 19:14:28
|
mattie, what I am posting below is a skeleton. First change the Tbl table name to your actual table name and run as it is to see what it produces. If it seems like what you are looking for, then look at the comments I have in the code to see what you need to add so it will pick up all required categories and add appropriate filters via the where clause.;WITH cte1 AS( SELECT Tech_name, CASE WHEN ( Turfs LIKE '%Azalea%' OR Turfs LIKE '%Prichard%' OR Turfs LIKE '%Saraland%' ) AND sch_profile NOT LIKE 'Call%' AND DATEPART(weekday, Sched_Date) = 1 THEN 'SunGreer' WHEN ( Turfs LIKE '%Airprt%' OR Turfs LIKE '%Semmes%' OR Turfs LIKE '%Springhill%' ) AND sch_profile NOT LIKE 'Call%' AND DATEPART(weekday, Sched_Date) = 1 THEN 'SunSchillinger' WHEN ( Turfs LIKE'%Old_Shell%' OR Turfs LIKE '%Skyline%' OR Turfs LIKE '%Theodore%' OR Turfs LIKE '%bayfront%' OR turfs LIKE '%Bell_Fontaine%' ) AND sch_profile NOT LIKE 'Call%' AND DATEPART(weekday, Sched_Date) = 1 THEN 'SunDemo' -- ADD ALL THE OTHER CATEGORIES HERE AS ADDITIONAL WHEN CLAUSES END AS Category FROM Tbl WHERE SCHED_DATE >= DATEADD(DAY, (DATEDIFF(DAY, 0, GETDATE()) / 7) * 7, 5) AND SCHED_DATE < DATEADD(DAY, (DATEDIFF(DAY, 0, GETDATE()) / 7) * 7, 7) -- ADD ALL YOUR OTHER WHERE CLAUSES HERE),cte2 AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Tech_name) AS RN FROM cte1)SELECT SunGreer, SunSchillinger, SunDemoFROM cte2PIVOT (MAX(tech_name) FOR Category IN ([SunGreer],[SunSchillinger],[SunDemo]))P |
|
|
mattie
Starting Member
13 Posts |
Posted - 2013-02-05 : 12:29:05
|
This works great. I love it! Thx for posting! Wish I knew the structure of how this works... |
|
|
|
|
|
|
|