I need a Special Summary SQL

jwcoving · August 7, 2015, 2:52pm

I have a table that I want to summarize by utilityno. However, If the detail has more than 36, I want two rows. One with 36 and the next with the balance. So If I have 42 records, I want 36 on one row and a count of 6 on the next.

Here is my SQL thus far
SELECT count(trdate) as Items
,[UtilityNo]
,[UtilityName1]
,Max([TrDate]) as Date
,Max([Branch]) as Branch
,Sum(CustAmount) as Amount

FROM [TellerApp2000SQL].[dbo].[UtilityDetail]
where trdate7 = 2015215 and branch = 4
-- Having mod(Items , 36)
group by utilityno,utilityname1
order by utilityno

Which gets me this

Items Name Amount
2 Northn 75.50
43 Magnolia r Assn 5,373.28
1 Friendship C, Inc 18.50
32 SunnyHill Water Assn 946.20
1 Fernwoodewerage Assn 79.74

So the Magnolia r Assn needs a row with 36 Items and another with 7.
If the same company had 105 items, I need 3 rows (36,36 and 33)

Sorry about the table above, I couldn't get it in a tabular form.
Thanks in advance.

Jim

ScottPletcher · August 7, 2015, 3:41pm

Make your existing query a derived table, then join it to a "standard" tally table of sequential numbers. If you don't have a tally table, I can try to post code to create one:

SELECT 
   CASE WHEN Items < 36 THEN Items
        WHEN Items - (tally - 1) * 36 > 36 THEN 36
        ELSE Items - (tally - 1) * 36 END AS Items
  ,[UtilityNo]
  ,[UtilityName1]
  ,Date
  ,Branch
  ,Amount    
FROM (
    SELECT Count(trdate) as Items
      ,[UtilityNo]
      ,[UtilityName1]
      ,Max([TrDate]) as Date
      ,Max([Branch]) as Branch
      ,Sum(CustAmount) as Amount
    FROM [TellerApp2000SQL].[dbo].[UtilityDetail]
    WHERE trdate7 = 2015215 AND branch = 4
    GROUP BY utilityno,utilityname1
) AS derived
INNER JOIN dbo.tally t ON t.tally BETWEEN 1 AND CEILING(Items/36.0)
ORDER BY utilityno

jwcoving · August 7, 2015, 4:18pm

Thanks for your quick response. I don't have any experience with derived tables. Please post a Tally table example.

Thanks,

Jim

jwcoving · August 7, 2015, 8:06pm

Scott:

Thanks for your SQL. I figured out the Tally Table Creation.

Jim

jwcoving · August 7, 2015, 8:21pm

One more item I need help with. I would like to have a column on the output that is the record no. ie Row 1 has a column with 1, Row 2 = 2 etc.

How is this added?

ScottPletcher · August 7, 2015, 9:26pm

Do you mean overall or for each UtilityNo/Name? If for each one, I think you just list the tally value in your result: it would be 1 for the first 36 (or less), 2 for the next 36 (or less), etc..

jwcoving · August 8, 2015, 4:59pm

For each Utility / Name. Tally ID is giving me 1,1,1,2,1 with the 4th record having more than 36. I need the first record to have 1, 2nd to have 2, 3rd to have 3.

I am using this number to drive a group header record in a report.

jwcoving · August 8, 2015, 5:30pm

I have hat I need. I added a column --> Row_Number() Over (Order By UtilityNO) as RowNo, but this gives me 1,2,3,4,,4,5. THe 4th group record has more than 36. The summary output is a total of 6 records so I need 1,2,3,4,5,6,

jwcoving · August 10, 2015, 6:54pm

Thank-you for your prompt responses. From the last post above, I almost have what I need. I need to be able to have the record number as part of my output. What is the SQL syntax to be able to include a row number regardless of more than in my case 36 detail summarized.

Thanks in advance.

Jim

jwcoving · August 12, 2015, 3:49pm

Scott:

Tally ID is not giving me the column value I need. I need the Column value to be the row number in the output. If the output results in 5 records, THen the column needs to have 1,2,3,4,5.

Thanks,

Jim

jwcoving · August 14, 2015, 3:39pm

I was wondering if there is an solution to my question or not. Can this be done?

JamesK · August 14, 2015, 3:53pm

If you add a row_number column in the OUTERMOST query, you should get a unique serial number. Assuming you are using Scott's original query, something like the following should give you unique serial numbers.

SELECT 
   ROW_NUMBER() OVER( ORDER BY [UtilityNo], [UtilityName]) as SerialNumber, --< Change columns in ORDER BY as required
   CASE WHEN Items < 36 THEN Items
        WHEN Items - (tally - 1) * 36 > 36 THEN 36
        ELSE Items - (tally - 1) * 36 END AS Items
  ,[UtilityNo],

Or, is that what you already did? Can you post your latest code that is giving you duplicate numbers?

jwcoving · August 15, 2015, 1:57pm

Here is my latest
SELECT
CASE WHEN Items < 36 THEN Items
WHEN Items - (ID - 1) * 36 > 36 THEN 36
ELSE Items - (ID - 1) * 36 END AS Items
,[UtilityNo]
,[UtilityName1]
,[Date]
,Branch
,Amount ,Rowno
FROM (
SELECT Count(trdate) as Items,Row_Number() Over (Order By Max(Trdate)) as RowNo
,[UtilityNo]
,[UtilityName1]
,Max([TrDate]) as Date
,Max([Branch]) as Branch
,Sum(CustAmount) as Amount
FROM [TellerApp2000SQL].[dbo].[UtilityDetail]
WHERE trdate7 = 2015215 AND branch = 15
GROUP BY utilityno,utilityname1
) AS derived
INNER JOIN [TellerApp2000SQL].[dbo].tally t ON t.ID BETWEEN 1 AND CEILING(Items/36.0)
ORDER BY utilityno

jwcoving · August 15, 2015, 2:03pm

ItemsUtilityNo UtilityName1 Date Branch Amount Rowno
36 2 Magnolia Electr Assn 2015-08-03 15 7210.83 2 13 2 Magnolia Elecr Assn 2015-08-03 15 7210.83 2
36 4 SouthMS Eleric Power Assn 2015-08-03 15 5369.25 3
1 4 SouthMS Eleric Power Assn 2015-08-03 15 5369.25 3
33 5 Lincoln Rural Water Assn 2015-08-03 15 820.90 1

hhrr · August 16, 2015, 7:18am

Hi

I worked on this .. This is how far i got ..

instead of "rownum%3" you can use "rownum%36"

drop table #Temp
create table #Temp
(
EmpName varchar(100) null,
UtilityNo int null,
UtilityName varchar(100) null,
Sal int null
)
insert into #Temp select 'John',1,'abc',100
insert into #Temp select 'John',1,'def',120
insert into #Temp select 'John',1,'xyz',130
insert into #Temp select 'John',1,'sdk',90

insert into #Temp select 'Kim',3,'abc',300
insert into #Temp select 'Kim',3,'def',320
insert into #Temp select 'Kim',3,'xyz',330
insert into #Temp select 'Kim',3,'sdk',90
insert into #Temp select 'Kim',3,'iii',900
insert into #Temp select 'Kim',3,'jjj',900
insert into #Temp select 'Kim',3,'kkk',900

;
with abc_cte as
(
SELECT row_number() over(partition by [UtilityNo] order by [UtilityName]) as rownum
,[UtilityNo]
,[UtilityName]
,Sum(sal) as Amount
FROM #Temp
group by utilityno,utilityname
)
select rownum%3,* from abc_cte

hhrr · August 18, 2015, 6:46am

Hi

I posted earlier ... I worked on it again
I got the result set .. Please see the solution

;
WITH abc_cte AS
(
SELECT row_number() OVER (PARTITION BY NAME ORDER BY NAME) AS rownum
,NAME
FROM #abc
)
,def_cte AS
(
SELECT dense_rank() OVER (PARTITION BY (rownum % 37) ORDER BY rownum) AS rank123
,NAME
,rownum
FROM abc_cte
)
SELECT NAME
,rank123
,count(rank123)
FROM def_cte
GROUP BY NAME,rank123
ORDER BY NAME,rank123

/*
drop table #abc
create table #abc
(
rownum int identity(1,1) not null ,
name varchar(100) null
)
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'a'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
insert into #abc(name) select 'b'
*/

hhrr · August 18, 2015, 7:44am

Hi

I replied ealier
I am posting the same thing again
-- with better data
-- with better formatting

;
WITH abc_cte AS
(
SELECT row_number() OVER (PARTITION BY NAME ORDER BY NAME) AS rownum
,NAME
FROM #abc
)
,def_cte AS
(
SELECT dense_rank() OVER (PARTITION BY (rownum % 37) ORDER BY rownum) AS rank123
,NAME
,rownum
FROM abc_cte
)
SELECT NAME
,rank123
,count(rank123)
FROM def_cte
GROUP BY NAME
,rank123
ORDER BY NAME
,rank123
/*
drop table #abc
create table #abc
(
name varchar(100) null
)
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
*/

hhrr · August 18, 2015, 10:22am

Hi

I posted above
Its the same thing with much better formatting

;
WITH abc_cte
AS (
	SELECT row_number() OVER (PARTITION BY NAME ORDER BY NAME) AS rownum
		,NAME
	FROM #abc
	)
	,def_cte
AS (
	SELECT dense_rank() OVER (PARTITION BY (rownum % 37) ORDER BY rownum) AS rank123
		,NAME
		,rownum
	FROM abc_cte
	)
SELECT NAME
	,rank123
	,count(rank123)
FROM def_cte
GROUP BY NAME
	,rank123
ORDER BY NAME
	,rank123

/*
drop table #abc
create table #abc
(
name varchar(100) null
)
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'harry'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
insert into #abc(name) select 'brown'
*/

jwcoving · August 19, 2015, 4:48pm

Thanks for all of your suggestions on this. If I just had the left most column (Row Header) as a column in my output, I would be happy. Is it possible to get the Row Header value?

Thanks

Kristen · August 20, 2015, 6:33am

The 1,2, 3, 4 ... number column?

If so then you could add a ROW_NUMBER() to the final select to add row numbers to the output.

	[RowNumber] = ROW_NUMBER()
		OVER
		(
			ORDER BY NAME, rank123
		),