April 16, 2009 at 12:11 pm
I having trouble figuring out how to display a certain dataset the way a customer wants it. basically My script returns 10 columns of data. The first 5 columns are duplicate/non-unique values, and the last 5 are unique. Example of current output
Accountname Booknumber Bookname
StoreOne 1 A1
StoreOne 2 A2
StoreOne 3 A3
StoreOne 4 A4
StoreOne 5 A5
StoreFive 1 A1
StoreFive 2 A2
StoreFive 3 A3
StoreFive 4 A4
StoreFive 5 A5
The customer wants to see the data presented like this:
Accountname Booknumber Bookname
StoreOne 1 A1
2 A2
3 A3
4 A4
5 A5
StoreFive 1 A1
2 A2
3 A3
4 A4
5 A5
How can I make the 'AccountName' column display the store name once, and not appear on every line with a unique value?
Thanks in advance.
April 16, 2009 at 12:19 pm
The formatting I put in the post got messed up, so I hope it makes sense what I'm trying to do.
April 16, 2009 at 12:28 pm
You can't do this in T-SQL, or not in any way that would make sense for a report. Typically the front end for your reporting would ignore the repetitions. How are you displaying the report?
April 16, 2009 at 12:32 pm
Hi Steve,
I'm executing the script manually and saving results to file (.xls)
I really wanted to modify the script so I could avoid any manual work on the excel file.
Could I accomplish this with SSIS?
April 16, 2009 at 12:35 pm
Perhaps, really this is a row by row operation, checking for a change in the first column, and if there isn't one, rewriting it with a blank.
I'll put out a note to see if anyone has a quick answer for this. My guess is there's some row_number query looking for the previous row that will work. It won't be efficient, and if this is run often, it would be an issue.
April 16, 2009 at 12:46 pm
Thanks for your help, Steve. This report would be run every week against a UAT instance of one of our production databases, so efficiency is not critical
April 16, 2009 at 12:50 pm
I don't know if this is going to work for you. If you displaying the data in a report you should have the report to deal with it. If you export it to excel or use somekind of data grid this might work
CREATE TABLE test
(
Accountname VARCHAR(10),
Booknumber INT,
Bookname VARCHAR(20)
)
INSERT INTO test (Accountname , Booknumber, Bookname) VALUES ('StoreOne', 1 ,'A1')
INSERT INTO test (Accountname , Booknumber, Bookname) VALUES ('StoreOne', 2 ,'A2')
INSERT INTO test (Accountname , Booknumber, Bookname) VALUES ('StoreOne', 3 ,'A3')
INSERT INTO test (Accountname , Booknumber, Bookname) VALUES ('StoreOne', 4 ,'A4')
INSERT INTO test (Accountname , Booknumber, Bookname) VALUES ('StoreOne', 5 ,'A5')
INSERT INTO test (Accountname , Booknumber, Bookname) VALUES ('StoreFive', 1 ,'A1')
INSERT INTO test (Accountname , Booknumber, Bookname) VALUES ('StoreFive', 2 ,'A2')
INSERT INTO test (Accountname , Booknumber, Bookname) VALUES ('StoreFive', 3 ,'A3')
INSERT INTO test (Accountname , Booknumber, Bookname) VALUES ('StoreFive', 4 ,'A4')
INSERT INTO test (Accountname , Booknumber, Bookname) VALUES ('StoreFive', 5 ,'A5')
SELECT
CASE
WHEN ROW_NUMBER() OVER (PARTITION BY Accountname ORDER BY Booknumber DESC) = 1 THEN Accountname
ELSE ''
END AS 'Accountname',
Booknumber, Bookname
FROM test
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 16, 2009 at 12:50 pm
Try this and this if this gets you what you want. I included my create and insert scripts too. 😉
create table Account
(
Accountname varchar(50),
Booknumber int,
Bookname varchar(50)
)
insert account select 'StoreOne', 1, 'A1'
insert account select 'StoreOne', 2, 'A2'
insert account select 'StoreOne', 3, 'A3'
insert account select 'StoreOne', 4, 'A4'
insert account select 'StoreOne', 5, 'A5'
insert account select 'StoreFive', 1, 'A1'
insert account select 'StoreFive', 2, 'A2'
insert account select 'StoreFive', 3, 'A3'
insert account select 'StoreFive', 4, 'A4'
insert account select 'StoreFive', 5, 'A5'
;with ViewAccount as (
select Accountname, Booknumber, Bookname, ROW_NUMBER() over (order by Accountname, Booknumber, Bookname) as rownum from Account
)
select case when (curr.Accountname next.AccountName) or curr.rownum = 1 then curr.AccountName else '' end, curr.Booknumber, curr.Bookname, curr.rownum
from ViewAccount curr
left join ViewAccount next on curr.rownum = next.rownum + 1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2009 at 1:01 pm
Thanks, Guys! I went with slange's suggestion first and it works great. THANK YOU!!!!
April 16, 2009 at 1:27 pm
Thanks for the update and glad it's working.
April 16, 2009 at 1:29 pm
My solution has a simpler execution plan and lower query cost so it might perform faster. But of course both them work and it's your call. 🙂
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 16, 2009 at 1:41 pm
JacekO (4/16/2009)
My solution has a simpler execution plan and lower query cost so it might perform faster. But of course both them work and it's your call. 🙂
I agree. The only thing he might need to change is the order by so it is the order he wanted. Yours is much simpler. I definitely learned something from your approach. Thanks! :smooooth:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2009 at 1:50 pm
Yours is much simpler.
Probably but not necessary. I think it looks simpler. 😀
But looks could be deceiving.
Remember we worked with a much simpler table then the final request. I think he needs to deal with 10 columns not 3 and I think he wants to hide 5 of them. So I would like to see the final code. Maybe josh.roy can compare both methods on his live data and show us the performance difference...
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 16, 2009 at 1:57 pm
Jacek, I will try your next for sure! Here is the code with Slanges suggestion. This script is for a developer here in my office, so i just made the temp table all varchars, and he can adjust accordingly.
set transaction isolation level read uncommitted
create table temp1
(CompanyNamevarchar(255),
companyIDvarchar (255),
ofcNovarchar(255),
ofcNamevarchar(255),
officeidvarchar (255),
OfcClaimMaskvarchar (255),
OfcTINMaskvarchar (255),
ofcEditsvarchar(255),
udfidnovarchar (255),
UDFTypevarchar(255),
UDFNamevarchar (255),
RequiredFieldvarchar (255),
UDFFormatvarchar (255),
textmaxlengthvarchar (255),
textmaskvarchar (255),
textenforcelength varchar (255),
restrictrangevarchar (255),
datestartvarchar (25),
dateendvarchar(25),
)
insert into TEMP1
select distinct
pc.companyName as 'Company Name',
pc.companyID as 'Database Company ID',
po.ofcNo as 'Office Number',
po.ofcName as 'Office Name',
po.officeid as 'Database Office ID',
CASE
WHEN Len(po.ofcClaimMask) = 0 THEN 'None'
WHEN po.ofcClaimMask is null THEN 'None'
ELSE replace(po.ofcClaimMask,'?','a')
END as 'Office Claim Mask',
CASE
WHEN po.ofcTinMask = '' THEN 'None'
WHEN po.ofcTinMask is null THEN 'None'
WHEN rtrim (po.ofcTinMask) = '' THEN 'None'
WHEN Len(po.ofctinmask) = 0 THEN 'None'
ELSE po.ofcTinMask
END as 'Office TIN Mask',
CASE
WHEN po.ofcEdits = 0 THEN 'Claim Mask Disabled - TIN Mask Disabled'
WHEN po.ofcEdits = 1 THEN 'Claim Mask Enabled - TIN Mask Disabled'
WHEN po.ofcEdits = 2 THEN 'Claim Mask Disabled - TIN Mask Enabled'
WHEN po.ofcEdits = 3 THEN 'Claim Mask Enabled - TIN Mask Enabled'
END as 'Claim / TIN MASK Enforced',
ul.udfidno as 'UDF #',
CASE
WHEN ScreenType = 1 THEN 'Bill'
WHEN ScreenType = 2 THEN 'Claim'
WHEN ScreenType = 3 THEN 'Claimant'
WHEN ScreenType = 4 THEN 'Provider'
END as 'UDF Type',
ul.udfname as 'UDF Name',
CASE
WHEN RequiredField = 0 THEN 'Disabled'
WHEN RequiredField = 1 THEN 'Enabled'
END as 'Required Field',
CASE
WHEN DataFormat = 1 THEN 'Currency'
WHEN DataFormat = 2 THEN 'Date/Time'
WHEN DataFormat = 3 THEN 'List'
WHEN DataFormat = 4 THEN 'Number'
WHEN DataFormat = 5 THEN 'Text'
WHEN DataFormat = 6 THEN 'Checkbox'
END as 'UDF Format',
ul.textmaxlength as 'Max Length',
ul.textmask as 'Mask Structure',
CASE
WHEN ul.textenforcelength = 0 THEN 'Disabled'
WHEN ul.textEnforceLength = 1 THEN 'Enabled'
END as 'Enforce Mask Length',
CASE
WHEN ul.restrictrange = 0 THEN 'Disabled'
WHEN ul.restrictRange = 1 THEN 'Enabled'
END as 'Restrict Range',
convert(varchar(25), ul.minvaldate, 110) as 'Date Range Start',
convert(varchar(25), ul.maxvaldate, 110) as 'Date Range End'
from udflibrary ul
JOIN prf_officeUdf pu on pu.udfidno = ul.udfidno
JOIN prf_office po on po.officeid = pu.officeid
join prf_company pc on pc.companyId = po.companyid
where po.officeid in (select officeid from prf_office)
order by pc.companyid, po.officeid
;with ViewTEMP1 as (select CompanyName
,companyID
,ofcNo
,ofcName
,officeid
,OfcClaimMask
,OfcTINMask
,ofcEdits
,udfidno
,UDFType
,UDFName
,RequiredField
,UDFFormat
,textmaxlength
,textmask
,textenforcelength
,restrictrange
,datestart
,dateend
,ROW_NUMBER()
OVER (order by CompanyName
,companyID
,ofcNo
,ofcName
,officeid
,OfcClaimMask
,OfcTINMask
,ofcEdits
,udfidno
,UDFType
,UDFName
,RequiredField
,UDFFormat
,textmaxlength
,textmask
,textenforcelength
,restrictrange
,datestart
,dateend) AS rownum from TEMP1)
select case when (curr.CompanyName next.CompanyName) or curr.rownum = 1 then curr.CompanyName else '' end as 'Company Name'
,case when (curr.companyID next.companyID) or curr.rownum = 1 then curr.companyID else '' end as 'Database Company ID'
,case when (curr.ofcNo next.ofcNo) or curr.rownum = 1 then curr.ofcNo else '' end as 'Office Number'
,case when (curr.ofcName next.OfcName) or curr.rownum = 1 then curr.ofcName else '' end as 'Office Name'
,case when (curr.officeid next.officeid) or curr.rownum = 1 then curr.officeid else ' ' end as 'Office ID'
,curr.OfcClaimMask
,curr.OfcTINMask
,curr.ofcEdits
,curr.udfidno
,curr.UDFType
,curr.UDFName
,curr.RequiredField
,curr.UDFFormat
,curr.textmaxlength
,curr.textmask
,curr.textenforcelength
,curr.restrictrange
,curr.datestart
,curr.dateend
from ViewTEMP1 curr
left join ViewTEMP1 next on curr.rownum = next.rownum + 1
drop table TEMP1
April 17, 2009 at 9:47 am
Hi Guys,
I've hit a bit of a snafu with this one. The script will need to be executed against 2000 servers as well, so the ROW_NUMBER() function will not work. What solution do you recommend for the script I previously posted?
Thanks!
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply