Column split based on another column value

Question

Column split based on another column value

Gift Peddie

SSC Guru

Points: 73570
More actions
June 19, 2007 at 12:07 pm

#177070

CREATE TABLE [dbo].[TIME_DETAIL](
      [VALUE_ADDED_FLAG_TME_DTL] [int] NOT NULL,
      [TIME_TME_DTL] [decimal](5, 2) NOT NULL,
      [LAST_UPDATE_DATE_TME_DTL] [datetime] NOT NULL,


VALUE_ADDED_FLAG_TME_DTL          TIME_TME_DTL
       0                                                     1.11
       0                                                      2.22
       1                                                      3.33
       1                                                      4.44
       1                                                      15.00
       0                                                       5.00
       0                                                       4.44
       0                                                       7.00
       0                                                       67.00
       0                                                        2.10
       0                                                       1.00

I want the time values that represent 0 in TIME_TME_DTL to be move to a separate column and the time value that represent 1 in TIME_TME_DTL to be moved to another column. Thanks in advance
Kind regards,
Gift Peddie

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Anders Pedersen SSChampion Points: 11410 More actions · Answer 1

Not sure I understand the question. Do you mean that you want the values in TIME_TME_DTL to go into different column depending on the value of VALUE_ADDED_FLAG_TME_DTL?

Should be a fairly simple job using a case statement to split it I would think.

create

table #t (

C1 int,

C2 int)

insert

into #t values (0,1)

insert

into #t values (0,2)

insert

into #t values (1,1)

insert

into #t values (1,5)

select

c1, case C1 when 1 then C2 else null end as '1 values', case C1 when 0 then C2 else null end as '0 values'

from

#t

Gift Peddie SSC Guru Points: 73570 More actions · Answer 2

No I want the values in the second column to be separated into two columns based on 0 or 1 in column one correspond to the values in column two and column two is time stored as DECIMAL not INT. At the moment I can concatenate both columns to do an easy column split. I am just looking for an elegant solution.

Kind regards,
Gift Peddie

Anders Pedersen SSChampion Points: 11410 More actions · Answer 3

hmm thought that was what my code showed (conceptually). Post sample of what you want it to look like after the split maybe I will understand better.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 4

Yes your code split the columns into two but it was part of a mile long report so based on relevance to the customers data I will use what I have now. The reason is the customer sometimes call it flag other times it is called status. Thanks for the suggestion.

Kind regards,
Gift Peddie

Column split based on another column value

Cookies on SQLServerCentral