rename column

Client liked the output, but (i guess he was thinking about exporting into Excel), the results column should be named “results_for_test” – so the name of the test was not only in the GUI but in the output.

Took a little longer than i expected.  I tried to create the table with the right column name, but had problems with trying to get the alias name right.  In hindsight, maybe making the column name different and not the alias might have worked.

Then i tried to write dynamic SQL to get the name of the lab test (@LAB) into the sp_name arguments list, but that was not going well.  Then i recalled that SQL is made to run with local variables, so i just created a second local variable, using the @LABS name and other words.  That finally worked.  I ended up looking at the column headings, and decided that i wanted to ‘quote’ the test, so instead of [results for Total PSA], the output was [result for ‘Total PSA’].  Might as well fuss with it while the iron is hot, rather than later when i have to try to remember all i was doing.

Create the table

IF OBJECT_ID('tempdb..##intermediateResults' , 'U') IS NOT NULL drop TABLE ##intermediateResults
set @cmd = 'CREATE TABLE [dbo].[##intermediateResults]
(
 [name] [varchar](20) NULL 
...
 ,[count_ovs] [int] NULL '
 if (@Lab is not null) 
 begin
 set @cmd = @cmd + ' 
 [result] [varchar](100) 
 , [lab_info] [varchar](100) ) '
 end
set @cmd = @cmd + ' 
) ON [PRIMARY] '

OK, at this point we have a table to select into, but the column name is wrong.  Since i know the name of the lab test, i can now create a local variable and rename the column.

declare @name varchar(40) = 'result for ''' + @lab + ''''
Execute tempdb..sp_rename 
'[tempdb].[dbo].[##intermediateResults].[result]', 
@name,'column';

And if you select ##intermediateResults now, you find that the results column is now named something  like ‘result for ‘PSA Total”, which is what we want.

select, replace and union all

We have some database entries with the word DOC in their name.  We have some database entries without that word, but which have the same other words, like: ABC and ABC Doc.  They appear to be different, in units and counts, but hard to tell.  I was told to make a comparison.

The first step was to get the ‘doc’s, which was a select where name like ‘%DOC’.  EasyPeasy.  I GUESS the second step could have been:  get a list of all names where exists name + ‘  ‘ + doc, but instead, i got all the docs in a temp table, then removed the ‘doc’ from the name and looked up those.  Lots of docs did not have shorter name entries, some did.

select distinct [name] into #nameTemp FROM [table] 
where name  like '%doc'

SELECT distinct [name], units, count(*) FROM [table] 
where name in (select rtrim(ltrim(replace(name, 'doc',''))) 
from #nameTemp) group by name, units
union
SELECT distinct [name], units, count(*) FROM [table] 
where namelike '%doc' group by name, units

So we get output of the name, units and count for the short names and then the doc names.  I had to take them into Excel and short them, but it worked and it was easy.

Only a little harder is using a CTE (common table expression) as the first select.  Make the first select as the inside query with ;WITH CTE as ( … query here …), put the results into a temp table (notice the first select into and the absence on the following) and sort that

drop table #nameTemp
;with CTE as
(
select distinct [name] FROM [table] where name like '%doc'
)
SELECT distinct [name], units, count(*) as count into #nameTemp
 FROM [table] 
where name in 
(select rtrim(ltrim(replace(name, 'doc',''))) from CTE)
 group by result_name, units
union
select [name], units, count(*) as count FROM [table] 
where name like '%doc' group by name, units
select * from #nameTemp order by result_name