• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

String Builder

I am trying to build a string based on the column values returned in a result set. Here is part of the result set that is returned.



pmap_no                   price       rank       Indicator
6081                     69.30      1      Y
6081            69.30      2      N
6081            60.30      3      N
6081            60.30      4      N
6081            60.30      5      N
6081            0.00      6      Y

The string I am trying to build comes from the price column and the indicator column. The string from this above result set would look like ’x-69.3-60.3-60.3-60.3-x

Here are the rules.

Start with the lowest rank.

If indicator value = ‘Y’ then always ‘x’

If the price (money data type) is a whole number such as 12.00 then just the number to the left of the decimal. Such as ‘12’. A value of 0.00 is just ‘0’.

When looking to right of the decimal and greater than 0, follow this pattern.

12.10 = ’12.1’
12.02 = ’12.02’
12.15 = ’12.15’
12.70 = ’12.7’


Finally, between each rank throw in a ‘-‘ to separate the ranks.


Thanks
0
rwaterz
Asked:
rwaterz
  • 4
  • 4
1 Solution
 
lwadwellCommented:
It looks ugly, but I could not figure out another way to format the number.  I custom function (UDF) would be useful (like Oracle's trim() which defaults to trimming spaces but can be overridden to trim other characters ... like '0')

;with test_data as (
select 6081 as pmap_no, 69.30 as price, 1 as rank, 'Y' as Indicator union all
select 6081 as pmap_no, 69.30 as price, 2 as rank, 'N' as Indicator union all
select 6081 as pmap_no, 60.32 as price, 3 as rank, 'N' as Indicator union all
select 6081 as pmap_no, 12.00 as price, 4 as rank, 'N' as Indicator union all
select 6081 as pmap_no, 60.03 as price, 4 as rank, 'N' as Indicator union all
select 6081 as pmap_no, 60.30 as price, 5 as rank, 'N' as Indicator union all
select 6081 as pmap_no,  0.00 as price, 6 as rank, 'Y' as Indicator 
)
select distinct pmap_no
     , stuff((select '-'+ case when Indicator = 'Y' then 'x' 
                               else cast(floor(price) as varchar) 
                                    + case when price % 1 * 10 != 0 then '.' + cast(floor(price % 1 * 10) as varchar) else '' end
                                    + case when price % .1 * 100 != 0 then cast(floor(price % .1 * 100) as varchar) else '' end
                               end
              from test_data v 
              where v.pmap_no = m.pmap_no
              order by rank
              for XML PATH('')), 1, 1, '') as string
from test_data m

Open in new window

0
 
Jared_SCommented:
Are your row numbers static? If so you could pivot the data set and then concatenate your string in the select statement

ex...
select isnull((convert(varchar,[1]),'X')+ '-'+isnull((convert(varchar,[2]),'X')+ etc...  as Column1
(
(SELECT price = case
when rank = 'Y' then null
else price
end, rank
FROM dataset) a
PIVOT (Sum(price) for rank in ('1','2','3','4','5','6')) as PVT

Otherwise  I don't see any other way to do it in SQL without using a cursor.
Good luck!
0
 
rwaterzAuthor Commented:
lwadwell,
Your solution is about half way there. If I was to give it a price of 23.20 it returns 23.00.2.00

Also if the price was just 23.00, I need it to return 23 with no decimal or the zeros to the right of the decimal.

jared_s
The row numbers are not static. 6 would be in most cases but not a guarantee.

Thanks for the help!
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
lwadwellCommented:
My results from the query posted as:
x-69.3-60.32-12-60.03-60.3-x

Open in new window

The part removing the digits is
cast(floor(price) as varchar) 
+ case when price % 1 * 10 != 0 then '.' + cast(floor(price % 1 * 10) as varchar) else '' end
+ case when price % .1 * 100 != 0 then cast(floor(price % .1 * 100) as varchar) else '' end

Open in new window

** technically it is stringing the number together by the bits when not 0
0
 
lwadwellCommented:
What do you get when you run this SQL?
select price, cast(floor(price) as varchar) 
              + case when price % 1 * 10 != 0 then '.' + cast(floor(price % 1 * 10) as varchar) else '' end
              + case when price % .1 * 100 != 0 then cast(floor(price % .1 * 100) as varchar) else '' end as fmt_price
from (select 23.00 as price union all
      select 15.43 union all
      select 88.08 union all
      select 12.3) v

Open in new window


** late edit ... changed 23 to 23.00
0
 
rwaterzAuthor Commented:
I ran your original query as is and get the same results as you, but when I run it against my real data, I get the odd ball results. The only thing that may be the difference is that my price datatype is money. Maybe???
0
 
rwaterzAuthor Commented:
When I ran this...

select price, cast(floor(price) as varchar)
              + case when price % 1 * 10 != 0 then '.' + cast(floor(price % 1 * 10) as varchar) else '' end
              + case when price % .1 * 100 != 0 then cast(floor(price % .1 * 100) as varchar) else '' end as fmt_price
from (select 23.00 as price union all
      select 15.43 union all
      select 88.08 union all
      select 12.3) v



It seemed to come out good.

fmt_price
23
15.43
88.08
12.3
0
 
rwaterzAuthor Commented:
I was able to get it to work with some slight modification.


case when PARSENAME(Price,2) > 0 then PARSENAME(Price,2) else '0'end +
                                                case when PARSENAME(Price,1) > 0 then
                                                      Case When RIGHT(PARSENAME(Price,1),1) = 0 then '.' + Left(PARSENAME(Price,1),1) Else
                                                            '.' + PARSENAME(Price,1) end else ''end


Thanks for the help.
0
 
lwadwellCommented:
Oh ... the money datatype.  Didn't think of that.  Glad you got it working.  I would have re-cast it as decimal first myself .. e.g.
;with test_data as (
select 6081 as pmap_no, cast(69.30 as money) as price, 1 as rank, 'Y' as Indicator union all
select 6081 as pmap_no, cast(69.30 as money) as price, 2 as rank, 'N' as Indicator union all
select 6081 as pmap_no, cast(60.32 as money) as price, 3 as rank, 'N' as Indicator union all
select 6081 as pmap_no, cast(12.00 as money) as price, 4 as rank, 'N' as Indicator union all
select 6081 as pmap_no, cast(60.03 as money) as price, 4 as rank, 'N' as Indicator union all
select 6081 as pmap_no, cast(60.30 as money) as price, 5 as rank, 'N' as Indicator union all
select 6081 as pmap_no, cast( 0.00 as money) as price, 6 as rank, 'Y' as Indicator 
)
select distinct pmap_no
     , stuff((select '-'+ case when Indicator = 'Y' then 'x' 
                               else cast(floor(cast(price as decimal)) as varchar) 
                                    + case when cast(price as decimal(12,2)) % 1 * 10 != 0 then '.' + cast(floor(cast(price as decimal(12,2)) % 1 * 10) as varchar) else '' end
                                    + case when cast(price as decimal(12,2)) % .1 * 100 != 0 then cast(floor(cast(price as decimal(12,2)) % .1 * 100) as varchar) else '' end
                               end
              from test_data v 
              where v.pmap_no = m.pmap_no
              order by rank
              for XML PATH('')), 1, 1, '') as string
from test_data m

Open in new window

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now