How to create a TSQL summarization and capitalization query?

Question

Need assistance on adding camel and summarizing columns. This is the code I have so far:

SELECT  
  CASE WHEN (GROUPING (State) = 1) THEN 'State Total:'
       ELSE ISNULL(State, 'UNKNOWN')
       END AS StateName,
  CASE WHEN (GROUPING(County) = 1) THEN 'County Total:'
       ELSE ISNULL(County, 'UNKNOWN')
       END AS CountyName,
  SUM(WellCount) AS WellCount
FROM tbl
GROUP BY State, County WITH ROLLUP

Having Code:

/*Pulling data and display with appropriate format and show summarized data

You have a simple table of data.

No additional table variables or temp tables should be used

Your task in your sql output:  

    1.  Capitalize the state names
    2.  Convert the county names to camel (meaning first letter is capitalized)
    3.  Show the city data along with county and state totals
    4.  Sort by state, county, city, then totals
*/
SET NOCOUNT ON;

--**--**--
DECLARE @tbl TABLE (State varchar(2), County varchar(40), City varchar(40), WellCount int)

INSERT @tbl VALUES ('ok','la flore','Mcalister',5)
INSERT @tbl VALUES ('ok','la flore','Savannah',2)
INSERT @tbl VALUES ('ok','hughes','Dustin',9)
INSERT @tbl VALUES ('tx','tarrant','Fort Worth',51)
INSERT @tbl VALUES ('tx','tarrant','Burleson',6)
INSERT @tbl VALUES ('tx','parKer','Weatherford',7)
INSERT @tbl VALUES ('ar','bryaNnt','Little Rock',12)
INSERT @tbl VALUES ('ar','bryaNnt','Ozark',12)
INSERT @tbl VALUES ('ar','reeD','Van Buren',46)
INSERT @tbl VALUES ('nm','saN Jaun','Farmington',3)
INSERT @tbl VALUES ('nm','saN Jaun','Bloomfield',3)
INSERT @tbl VALUES ('nm','rio arriba','Durango',104)
--**--**--

The sample output should look like the following:

StateName CountyName     CityName        WellCount
AR        Bryannt        Little Rock     12
AR        Bryannt        Ozark           12
AR        Bryannt        County Total:   24
AR        Reed           Van Buren       46
AR        Reed           County Total:   46
AR        State Total:                   70
NM        Rio Amiba      Durango         104
NM        Rio Amiba      County Total:   104
NM        San Juan       Bloomfield      3
NM        San Juan       Famington       3
NM        San Juan       County Total:   6

You should edit your question to have, er, a question. The question in the title doesn't really make sense. Sample data, desired results, and table layouts are helpful. — Gordon Linoff, Jan 12 '15 at 03:30
Have you spoken with your instructor to get their assistance or improve your understanding of the material presented in class? — alroc, Jan 12 '15 at 04:00
A quick search found this as a possible function. Be aware of plagiarism, or it will NOT end well for you or your assignment. http://stackoverflow.com/questions/230138/sql-server-make-all-upper-case-to-proper-case-title-case — Jane S, Jan 12 '15 at 04:04

score 0 · Answer 1 · answered Jan 12 '15 at 11:11

If it work for other sample data then it can be further optimize.

Try like this,

DECLARE @tbl TABLE (State varchar(2), County varchar(40), City varchar(40), WellCount int)

INSERT @tbl VALUES ('ok','la flore','Mcalister',5)
INSERT @tbl VALUES ('ok','la flore','Savannah',2)
INSERT @tbl VALUES ('ok','hughes','Dustin',9)
INSERT @tbl VALUES ('tx','tarrant','Fort Worth',51)
INSERT @tbl VALUES ('tx','tarrant','Burleson',6)
INSERT @tbl VALUES ('tx','parKer','Weatherford',7)
INSERT @tbl VALUES ('ar','bryaNnt','Little Rock',12)
INSERT @tbl VALUES ('ar','bryaNnt','Ozark',12)
INSERT @tbl VALUES ('ar','reeD','Van Buren',46)
INSERT @tbl VALUES ('nm','saN Jaun','Farmington',3)
INSERT @tbl VALUES ('nm','saN Jaun','Bloomfield',3)
INSERT @tbl VALUES ('nm','rio arriba','Durango',104)

;WITH CTE AS
(
    select *,   DENSE_RANK()over( order by state)rn 
    ,ROW_NUMBER()over(partition by state order by wellcount)rn1 
    ,DENSE_RANK()over(partition by state order by wellcount)rn2
    from @tbl
)
,CTE2 AS
(
    SELECT State ,County ,City ,WellCount,RN,RN1,rn2,0 rn3  FROM CTE 

  UNION ALL

      select a.State ,a.County ,'County Total' ,
      case when a.rn2=b.rn2 then a.WellCount+b.wellcount 
      else case when a.rn1-b.rn1=1 then a.WellCount end end ,a.RN,a.RN1,a.rn2 ,1 rn3
      from cte a inner join cte b on a.rn=b.rn  and (a.rn1-b.rn1=1 )

 UNION ALL

      select a.State ,'State Total' ,'' , a.WellCount+b.wellcount +a.wellcount
        ,a.RN,a.RN1,a.rn2 ,2 rn3
      from cte a inner join cte b on a.rn=b.rn  and (b.rn1-a.rn1=1 ) and b.rn2-a.rn2=1


)
SELECT   * FROM CTE2
ORDER BY rn,wellcount,rn3

score 0 · Answer 2 · answered Feb 11 '16 at 15:16

I use this function:

    CREATE FUNCTION [dbo].[fs_PascalCase]
    (
        @Text nVarChar(MAX)

    )
    RETURNS nVarChar(MAX)
    AS
    BEGIN
            SET @Text = LOWER(@Text)

            DECLARE @New NVARCHAR(MAX) = (CASE WHEN @Text IS NULL THEN NULL ELSE '' END)

            DECLARE @Len INT = LEN(REPLACE(@Text, ' ', '_'))

            DECLARE @Index INT = 1

            WHILE (@Index <= @Len)
                IF (SUBSTRING(@Text, @Index, 1) LIKE '[^a-z]' AND @Index + 1 <= @Len)
                    SELECT @New = @New + UPPER(SUBSTRING(@Text, @Index, 2)), @Index = @Index + 2
                ELSE
                    SELECT @New = @New + SUBSTRING(@Text, @Index, 1) , @Index = @Index + 1

            RETURN ( UPPER(LEFT(@New, 1)) + RIGHT(@New, ABS(@Len - 1)) )

    END

    GO

How to create a TSQL summarization and capitalization query?

2 Answers2