concatenante values from diff columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

concatenante values from diff columns

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2012-03-08 : 12:05:46

Hi,
I have data in my table as :-
c1,c2,c3,c4,c5,c6 are column names and
rest 2 lines are data inside it

c1 c2 c3 c4 c5 c6
1 cc two ranger good 78675
2 tt eight diver average 96868

I want:-

c11c2ccc3twoc4rangerc5goodc678675
c12c2ttc3eightc4diverc5averagec696868

that is all data are concatenated and EACH column names comes before the data.

How to do?

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 12:16:39

sounds a strange requirement but it can done as follows


SELECT 'c1' + cast(c1 as varchar(10)) + 'c2'+c2+'c3'+c3+'c4'+c4+'c5'+c5+'c6'+CAST(c6 as varchar(10))
FROM table

if any of above columns are nullable you need to handle them using COALESCE(column,'')
in that case do you need to include columname at all or you need to ignore it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2012-03-08 : 12:36:45

Perfect visakh.
No there will be no zeros in data.

One more thing,
Here first column c1 is a counter , values are increased from 1, 2, 3 ....

I want it to start from 001 But i don't want increasing.
For ex the 2 sample rows of data should have value:
001
001

Every time I run the code it should be 001 all values for c1 column..
teh next time i run code, it should be 002 for all values for c1 column.

Thanks

Regards,
Sushant
DBA
Virgin Islands(U.K)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-08 : 14:46:52

quote:
Originally posted by skybvi

Every time I run the code it should be 001 all values for c1 column..
teh next time i run code, it should be 002 for all values for c1 column.

Do you have a sturcture (table) to keep track of how often you run this "query?"

skybvi
Posting Yak Master

193 Posts

Posted - 2012-03-08 : 14:50:08

quote:
Originally posted by Lamprey

quote:
Originally posted by skybvi

Every time I run the code it should be 001 all values for c1 column..
teh next time i run code, it should be 002 for all values for c1 column.
Do you have a sturcture (table) to keep track of how often you run this "query?"

hi LAMPREY,
I dont have structure, but i was thinking like in C language we have, i =i+1 in while loop
Similiar in t sql i can put value = 001 + 001
each time jsut before my code i can put somehthing like that..

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 14:55:00

quote:
Originally posted by skybvi

Perfect visakh.
No there will be no zeros in data.

One more thing,
Here first column c1 is a counter , values are increased from 1, 2, 3 ....

I want it to start from 001 But i don't want increasing.
For ex the 2 sample rows of data should have value:
001
001

Every time I run the code it should be 001 all values for c1 column..
teh next time i run code, it should be 002 for all values for c1 column.

Thanks

Regards,
Sushant
DBA
Virgin Islands(U.K)

so is it kind of batch id you want?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2012-03-08 : 15:00:08

quote:
Originally posted by visakh16

quote:
Originally posted by skybvi

Perfect visakh.
No there will be no zeros in data.

One more thing,
Here first column c1 is a counter , values are increased from 1, 2, 3 ....

I want it to start from 001 But i don't want increasing.
For ex the 2 sample rows of data should have value:
001
001

Every time I run the code it should be 001 all values for c1 column..
teh next time i run code, it should be 002 for all values for c1 column.

Thanks

Regards,
Sushant
DBA
Virgin Islands(U.K)

so is it kind of batch id you want?

You hit the bull's eye :)

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 15:05:21

The best way for this would be to create a seperate batchidgenerator table which will have a identity column (batchid), then datecreated and createduser
each time before your insertion, insert a record in this table to generate a new identity value and return it using scope_identity() and use it as the batch id

so something like


DECLARE @BatchID int
INSERT INTO BatchIDGenerator (DateCreated,CreatedUser)
VALUES(GETDATE(),SUSER_SNAME())

SET @BatchID = SCOPE_IDENTITY()

SELECT 'c1' + cast(@BatchID as varchar(10)) + 'c2'+c2+'c3'+c3+'c4'+c4+'c5'+c5+'c6'+CAST(c6 as varchar(10))
FROM table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2012-03-09 : 15:21:38

Hi visakh,
i RUN THIS :-

DECLARE @BatchID int
INSERT INTO BatchIDGenerator (DateCreated,CreatedUser)
VALUES(GETDATE(),SUSER_SNAME())

SET @BatchID = SCOPE_IDENTITY()
SELECT cast(@BatchID as varchar(10))
+CCO+'PNO'+PNO+'BNA'+RTRIM(BNA)+
'UPC'+RTRIM(UPC)+LTRIM('DNO')+DNO+'UME'+RTRIM(CAST(UME as varchar(3)))+LTRIM('DN1')+RTRIM(DN1)+
'UPR'+CAST(UPR AS NVARCHAR(5))

FROM TEMP8#

I am getting NULL output in all 7 rows. i ensured temp8# has values in it.

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 21:42:55

did you create identity column in BatchIDgenerator table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2012-03-13 : 16:50:59

quote:
Originally posted by visakh16

did you create identity column in BatchIDgenerator table?

You were correct, I hadn't created..... After creation, Iam getting output.
Also, how to get a delimiter like ý between the values so as the application should understand the fields.

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-13 : 22:57:12

[code]SELECT 'c1' + cast(@BatchID as varchar(10)) + 'ýc2'+c2+'ýc3'+c3+'ýc4'+c4+'ýc5'+c5+'ýc6'+CAST(c6 as varchar(10))
FROM table
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-03-14 : 11:04:17

quote:
Originally posted by visakh16

if any of above columns are nullable you need to handle them using COALESCE(column,'')
in that case do you need to include columname at all or you need to ignore it?

quote:
Originally posted by skybvi

Perfect visakh.
No there will be no zeros in data.

skybvi, just so you're clear, NULLs are not zeros. NULL is unknown. Zero is a definite value. They need to be handled differently. Seems you have your solution for this issue, but it is an important distinction.

skybvi
Posting Yak Master

193 Posts

Posted - 2012-03-14 : 13:35:17

quote:
Originally posted by russell

quote:
Originally posted by visakh16

if any of above columns are nullable you need to handle them using COALESCE(column,'')
in that case do you need to include columname at all or you need to ignore it?

quote:
Originally posted by skybvi

Perfect visakh.
No there will be no zeros in data.

skybvi, just so you're clear, NULLs are not zeros. NULL is unknown. Zero is a definite value. They need to be handled differently. Seems you have your solution for this issue, but it is an important distinction.

Thanks for reminding, point noted.

Regards,
Sushant
DBA
Virgin Islands(U.K)

skybvi
Posting Yak Master

193 Posts

Posted - 2012-03-16 : 15:10:24

quote:
Originally posted by visakh16

[code]SELECT 'c1' + cast(@BatchID as varchar(10)) + 'ýc2'+c2+'ýc3'+c3+'ýc4'+c4+'ýc5'+c5+'ýc6'+CAST(c6 as varchar(10))
FROM table

How to differenciate between Header and records

For example i want to get
1 line as header and 10 records in it.

WRT to the same contancentating columns.
Thanks.

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 15:12:37

quote:
Originally posted by skybvi

quote:
Originally posted by visakh16

[code]SELECT 'c1' + cast(@BatchID as varchar(10)) + 'ýc2'+c2+'ýc3'+c3+'ýc4'+c4+'ýc5'+c5+'ýc6'+CAST(c6 as varchar(10))
FROM table

How to differenciate between Header and records

For example i want to get
1 line as header and 10 records in it.

WRT to the same contancentating columns.
Thanks.

Regards,
Sushant
DBA
Virgin Islands(U.K)

show a data sample for this. I'm not getting what exactly you're asking for

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2012-03-16 : 15:36:54

quote:
Originally posted by visakh16

quote:
Originally posted by skybvi

quote:
Originally posted by visakh16

[code]SELECT 'c1' + cast(@BatchID as varchar(10)) + 'ýc2'+c2+'ýc3'+c3+'ýc4'+c4+'ýc5'+c5+'ýc6'+CAST(c6 as varchar(10))
FROM table

How to differenciate between Header and records

For example i want to get
1 line as header and 10 records in it.

WRT to the same contancentating columns.
Thanks.

Regards,
Sushant
DBA
Virgin Islands(U.K)

show a data sample for this. I'm not getting what exactly you're asking for

Hi,
I am running 2 tsql statements like this :--

select distinct 'BNAPriceChangeFile'+
'ýBNO'
+BNA +
'ýBDA'+BDA +
'ýBTI'+BTI
+'ýICO'+ICO+'ý'
from temp8#

SELECT
CC
+'ýPNO'+PNO+
'ýUPC'+RTRIM(UPC)+LTRIM('ýDNO')+DNO+'ýUME'+RTRIM(CAST(UME as varchar(3)))+LTRIM('ýDN1')+RTRIM(DN1)+
'ýUPR'+CAST(UPR AS NVARCHAR(5))+'ý'

from temp8#

The first statement is yielding 1 header and
teh 2nd statement is yielding 9 rows.

Sample data is :--

header :--
BNAPriceChangeFileýBNO1ýBDAMar 16 2012ýBTI14:50:13ýICO9ý

Rows:-
CCOSPPCýPNO719ýUPC20071900000ýDNO060ýUMELBýDN1CHICKEN FRYERS WHOLE GRADE A LB - (40LB)ýUPR2.05ý
CCOSPPCýPNO725ýUPC20072500000ýDNO060ýUMELBýDN1CHICKEN FRYERS WHOLE GRADE A LB - (40LB)ýUPR2.05ý..
like this 7 more rows.

i WANT TO put hte output in an txt file automatically..
how to do it?
I am using import/export task from SSMS
and iam gettign errors.

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 15:52:35

using UNION ALL to merge the two selects and add an order by based on derived integer field. Make it 1 for header select and 2 for detail section

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2012-03-23 : 11:04:47

quote:
Originally posted by visakh16

using UNION ALL to merge the two selects and add an order by based on derived integer field. Make it 1 for header select and 2 for detail section

Cool, thanks its working good...
Can you tell how to put the union all select statements into 1 table ?

The main purpose is that I want to create a txt file from the output and put it in a c:\ of a server so that the application can access it and read it.

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-23 : 19:57:41

it will be like


SELECT * INTO NewTableName
FROM
(
the UNION ALL query
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2012-03-26 : 13:04:40

quote:
Originally posted by visakh16

it will be like

SELECT * INTO NewTableName
FROM
(
the UNION ALL query
)t

Hi,
I put up as you stated, i am getting errors....
see my sample data and errors:-

SELECT * INTO NewTableName
FROM
(
select distinct 'BNAPriceChangeFile'+
'ýBNO'
+BNA +
'ýBDA'+BDA +
'ýBTI'+BTI
+'ýICO'+ICO+'ý'
+'SNO1ý'
from temp8#
union all
SELECT
CC
+'ýPNO'+PNO+
'ýUPC'+RTRIM(UPC)+LTRIM('ýDNO')+DNO+'ýUME'+RTRIM(CAST(UME as varchar(3)))+LTRIM('ýDN1')+RTRIM(DN1)+
'ýUPR'+CAST(UPR AS NVARCHAR(5))+'ý'

from temp8#

--2nd store
union all

select distinct 'BNAPriceChangeFile'+
'ýBNO'
+BNA +
'ýBDA'+BDA +
'ýBTI'+BTI
+'ýICO'+ICO+'ý'
+'SNO2ý'
from temp8#

union all
SELECT
CC
+'ýPNO'+PNO+
'ýUPC'+RTRIM(UPC)+LTRIM('ýDNO')+DNO+'ýUME'+RTRIM(CAST(UME as varchar(3)))+LTRIM('ýDN1')+RTRIM(DN1)+
'ýUPR'+CAST(UPR AS NVARCHAR(5))+'ý'

from temp8#

)t

ERROR:-

Msg 8155, Level 16, State 2, Line 1
No column was specified for column 1 of 't'.
Msg 1038, Level 15, State 5, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

Regards,
Sushant
DBA
Virgin Islands(U.K)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources