combining data into one field - SQL Server Forums

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)

combining data into one field

Author

Topic

amber
Starting Member

7 Posts

Posted - 2008-04-14 : 17:56:05

Hello,
I'm not sure if this is possible, but here is what I'm trying to do:

I have a table that contains for example the following:

SPECIES PERCENT
s 19
P 25
B 32
D 24

I want to end up with a single field, titled S_P_B_D with the values 19_25_32_24.

Possible?
Thanks in advance.
Amber

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-14 : 18:11:01

How's this?

I've added an id column so you have control over the order of things.

-- Data
DECLARE	@t TABLE (id int identity(1, 1), SPECIES char(1), [PERCENT] int)
insert @t
          select 'S', 19
union all select 'P', 25
union all select 'B', 32
union all select 'D', 24

-- Calculation
declare @SPECIES varchar(30), @PERCENT varchar(30)
select @SPECIES = isnull(@SPECIES + '_', '') + SPECIES,
       @PERCENT = isnull(cast(@PERCENT as varchar(30)) + '_', '') + cast([PERCENT] as varchar(30))
from @t order by id

select @SPECIES as 'SPECIES', @PERCENT as 'PERCENT'

/* Results
SPECIES                        PERCENT
------------------------------ ------------------------------
S_P_B_D                        19_25_32_24
*/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 18:11:48

Are there always exact same number of specied, named the exact same way?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 18:15:25

A slight variation of Ryans suggestion

DECLARE	@t TABLE (id int identity(1, 1), SPECIES char(1), [PERCENT] int)
insert @t
          select 'S', 19
union all select 'P', 25
union all select 'B', 32
union all select 'D', 24

-- Calculation
declare @SPECIES varchar(30), @PERCENT varchar(30)

select @SPECIES = isnull(@SPECIES + '_', '') + SPECIES,
       @PERCENT = isnull(cast(@PERCENT as varchar(30)) + '_', '') + cast([PERCENT] as varchar(30))
from @t order by id

declare @sql varchar(8000)
set @sql = 'select ''' + @percent + ''' as ' + @species

exec(@sql)

E 12°55'05.25"
N 56°04'39.16"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-14 : 18:20:14

quote:
declare @sql varchar(8000)
set @sql = 'select ''' + @percent + ''' as ' + @species

exec(@sql)

Good point...

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

amber
Starting Member

7 Posts

Posted - 2008-04-14 : 18:27:09

I have a few more details -
Species are always present, and in the same order (S_B_P_D) but sometimes don't have an existing record in the database (so if there are no "P" species - "P" still displays, but associated value = '0'.

Thanks!
Amber

quote:
Originally posted by Peso

Are there always exact same number of specied, named the exact same way?

E 12°55'05.25"
N 56°04'39.16"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-14 : 18:37:54

Like this?

-- Data
DECLARE	@t2 TABLE (SPECIES char(1), [PERCENT] int)
insert @t2
          select 'S', 19
union all select 'B', 32
union all select 'D', 24

-- Calculation
DECLARE	@t1 TABLE (id int identity(1, 1), SPECIES char(1))
insert @t1
          select 'S'
union all select 'P'
union all select 'B'
union all select 'D'

declare @SPECIES varchar(30), @PERCENT varchar(30)

select @SPECIES = isnull(@SPECIES + '_', '') + a.SPECIES,
       @PERCENT = isnull(cast(@PERCENT as varchar(30)) + '_', '') + cast(isnull([PERCENT], 0) as varchar(30))
from @t1 a left outer join @t2 b on a.SPECIES = b.SPECIES order by id

declare @sql varchar(8000)
set @sql = 'select ''' + @percent + ''' as ' + @species

exec(@sql)

/* Results
S_P_B_D
----------
19_0_32_24
*/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 18:40:25

[code]SELECT CAST(S AS VARCHAR(3))
+ '_'
+ CAST(B AS VARCHAR(3))
+ '_'
+ CAST(P AS VARCHAR(3))
+ '_'
+ CAST(D AS VARCHAR(3)) AS [S_B_P_D]
FROM (
SELECT MAX(CASE WHEN Species = 'S' THEN [Percent] ELSE 0 END) AS S,
MAX(CASE WHEN Species = 'B' THEN [Percent] ELSE 0 END) AS B,
MAX(CASE WHEN Species = 'P' THEN [Percent] ELSE 0 END) AS P,
MAX(CASE WHEN Species = 'D' THEN [Percent] ELSE 0 END) AS D
FROM YourTableNameHere
) AS d[/code]

E 12°55'05.25"
N 56°04'39.16"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-14 : 18:46:11

Switch B and P and we're in business

amber - Peso's query there will be more efficient for your specific case, so you should use that if you don't need something more general.

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 18:56:26

Nah... According to post made 04/14/2008 : 18:27:09, the order of species is now altered.

E 12°55'05.25"
N 56°04'39.16"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-14 : 19:03:37

lol - fair cop

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

amber
Starting Member

7 Posts

Posted - 2008-04-15 : 12:37:06

Thanks for all of your help!!

So now I have the script below, but receive the error "FROM keyword not found where expected"...

SELECT CAST(S AS VARCHAR(3))
+ '_'
+ CAST(B AS VARCHAR(3))
+ '_'
+ CAST(Pl AS VARCHAR(3))
+ '_'
+ CAST(D AS VARCHAR(3)) AS [S_B_Pl_D]
FROM (
SELECT MAX(CASE WHEN SPEC_SPECIES_ID = 'S' THEN [BLKC_DISTRIBUTION_PCT] ELSE 0 END) AS S,
MAX(CASE WHEN SPEC_SPECIES_ID = 'B' THEN [BLKC_DISTRIBUTION_PCT] ELSE 0 END) AS B,
MAX(CASE WHEN SPEC_SPECIES_ID = 'Pl' THEN [BLKC_DISTRIBUTION_PCT] ELSE 0 END) AS Pl,
MAX(CASE WHEN SPEC_SPECIES_ID = 'D' THEN [BLKC_DISTRIBUTION_PCT] ELSE 0 END) AS D
FROM V_BLOCK_CRUISE_STATS
) AS d
/

Cheers,
Amber

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 13:19:35

is this your full query used? dont think there's anything wrong with it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 15:24:59

Maybe because PI is a reserved word?

It wasn'r PI before, just P.

E 12°55'05.25"
N 56°04'39.16"

amber
Starting Member

7 Posts

Posted - 2008-04-15 : 17:21:28

it's Pl (letter L not I).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-15 : 17:37:38

Your query is syntactically correct in T-SQL. I googled your error since I hadn't seen that wording before. It looks like it's an Oracle error.

Are you using Oracle? If so, you'll have better luck on an Oracle forum. This site is for Microsoft SQL Server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-16 : 03:21:48

quote:
Originally posted by tkizer

Your query is syntactically correct in T-SQL. I googled your error since I hadn't seen that wording before. It looks like it's an Oracle error.

Are you using Oracle? If so, you'll have better luck on an Oracle forum. This site is for Microsoft SQL Server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Yes it is. In ORACLE the keyword AS should not be preceded by alias table name

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources