August 3, 2011 at 10:34 am
I have this data
SiteProductReleaseTypeCnt
Company NameProduct12005Upgrade1
Company NameProduct12006Upgrade2
Company NameProduct12007New1
Company NameProduct22002New1
Company NameProduct22004New1
I am being asked to insert the following into a new a column in a new table. I am having difficulty in get this done and tested. WHen I use Char(10) + Char(13) + Char(9), I get a single line of text displayed. I apprecate any ideas (including a change to the presentation requirement).
Site: Company Name
Product: Product1
Release: 2005
Type: Upgrade : 1
Release: 2006
Type: Upgrade : 2
Release: 2007
Type: New : 1
Product: Product 2
Release: 2002
Type: New : 1
Release: 2004
Type: New : 1
Thanks,
Bill
August 3, 2011 at 10:45 am
Hi
At first sight I'd suggest you post your CREATE TABLE, INSERT and SELECT statements so that we can get a clearer picture of what you're trying to do.
Regarding your column names, it looks like 'Type' and 'Cnt' should be separate columns but you have put the information for both columns together. Was this intentional?
🙂
August 3, 2011 at 10:56 am
The tabs were lost in the initial message. Here are the statements to create table and test data:
create table regs (
site varchar(100),
product varchar(100),
release varchar(10),
type varchar(10),
cnt int
)
insert into regs values('Company Name','Product1','2005','Upgrade',1)
insert into regs values('Company Name','Product1','2006','Upgrade',2)
insert into regs values('Company Name','Product1','2007','New',1)
insert into regs values('Company Name','Product2','2002','New',1)
insert into regs values('Company Name','Product2','2004','New',1)
Here is a look of the data where the initial character of each line is a period.
Site: Company Name
. Product: Product1
. Release: 2005
. Type: Upgrade : 1
. Release: 2006
. Type: Upgrade : 2
. Release: 2007
. Type: New : 1
. Product: Product 2
. Release: 2002
. Type: New : 1
. Release: 2004
. Type: New : 1
Thanks,
Bill
August 3, 2011 at 11:06 am
Another attempt at showing the desired display. The .... would be replaced with tabs or spaces.
The resulting delivery table is one row per company.
Site: Company Name
.....Product: Product1
.......Release: 2005
.........Type: Upgrade : 1
.......Release: 2006
.........Type: Upgrade : 2
.......Release: 2007
.........Type: New : 1
....Product: Product 2
.......Release: 2002
.........Type: New : 1
.......Release: 2004
........Type: New : 1
August 3, 2011 at 11:17 am
If you are looking at the results in SSMS grid you will only get a single line. the Char(13) + Char(10) of ANSI (or is it ASCII?) new line will work.
DECLARE @TestVar AS VARCHAR(50)
SELECT
@TestVar = 'Hello World' + CHAR(13) + CHAR(10) + 'This is a Second Line'
PRINT @TestVar
This will show in the messages window the output of the second line. A note, if you reverse the CHAR(13) + CHAR(10) to be CHAR(10) + CHAR(13) you will get 2 new lines.
As to your design requirement, it seems arbitrary and not something that the DB is supposed to do... that is a presentation layer operation not a data layer, can it be done in SQL? yes. Should it? probably not. (just my $0.02)
August 3, 2011 at 11:59 am
Thanks!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply