Converting Bytes to KB, MB or GB

I was looking for some algorithm on how to convert Bytes to KB or MB or GB depending on the value. Most of the algorithm that I saw are using case statement to implement the breakdown as brackets.

Here's my own version. This will generate some random number that will be converted to KB, MB or GB. But this should help me in the future.

;with Sorted
as
(
   select 1 as base
   union all
   select base = base + 1
   from Sorted
   where base < 20
), Randomized
as
(
   select top 5 base
   from Sorted
   order by newid()
), SizeInStorage
as
(
select
   ByteSize = cast(abs(checksum(newid())) as bigint) 
from randomized
)
select
   ByteSize,
   Formatted = replace(convert(varchar(20), cast(ByteSize as money),1),'.00',''),
   Converted = coalesce(nullif(ByteSize/power(cast(1024 as bigint),4),0),
                        nullif(ByteSize/power(cast(1024 as bigint),3),0),
                        nullif(ByteSize/power(cast(1024 as bigint),2),0),
                        nullif(ByteSize/power(cast(1024 as bigint),1),0), 
                        ByteSize
                        ),
   Unit = coalesce('TB' + space(nullif(ByteSize/power(cast(1024 as bigint),4),0)), 
                   'GB' + space(nullif(ByteSize/power(cast(1024 as bigint),3),0)), 
                   'MB' + space(nullif(ByteSize/power(cast(1024 as bigint),2),0)), 
                   'KB' + space(nullif(ByteSize/power(cast(1024 as bigint),1),0)), 
                   'Bytes'
                  )
from SizeInStorage

Here's a sample result set:

ByteSize             Formatted       Converted   Unit
-------------------- --------------- ----------- -----
926528091            926,528,091     883         MB 
105563351            105,563,351     100         MB 
1603808599           1,603,808,599   1           GB 
866882480            866,882,480     826         MB 
426406913            426,406,913     406         MB 

Update...I want to convert bytes with two decimal places.

;with Sorted
as
(
   select 1 as base
   union all
   select base = base + 1
   from Sorted
   where base < 20
), Randomized
as
(
   select top 5 base
   from Sorted
   order by newid()
), SizeInStorage
as
(
select
   ByteSize = cast(abs(checksum(newid())) as bigint)
from randomized
union all
select top 1
   ByteSize = substring(cast(abs(checksum(newid())) as varchar(10)),1,5)
from randomized
union all
select top 1
   ByteSize = substring(cast(abs(checksum(newid())) as varchar(10)),1,3)
from randomized
)
select
   ByteSize,
   Formatted = replace(convert(varchar(20), cast(ByteSize as money),1),'.00',''),
   Converted = convert(varchar(30), isnull(cast(ByteSize as money)/ cast(coalesce((nullif((ByteSize/power(cast(1024 as bigint),4)),0)/nullif((ByteSize/power(cast(1024 as bigint),4)),0)) * power(cast(1024 as bigint),4),
                        (nullif((ByteSize/power(cast(1024 as bigint),3)),0)/nullif((ByteSize/power(cast(1024 as bigint),3)),0)) * power(cast(1024 as bigint),3),
                        (nullif((ByteSize/power(cast(1024 as bigint),2)),0)/nullif((ByteSize/power(cast(1024 as bigint),2)),0)) * power(cast(1024 as bigint),2),
                        (nullif((ByteSize/power(cast(1024 as bigint),1)),0)/nullif((ByteSize/power(cast(1024 as bigint),1)),0)) * power(cast(1024 as bigint),1)) as money)                                               
                        ,
                        ByteSize * 1.00
                           ), 1),
   Unit = coalesce('TB' + space(nullif(ByteSize/power(cast(1024 as bigint),4),0)),
                   'GB' + space(nullif(ByteSize/power(cast(1024 as bigint),3),0)),
                   'MB' + space(nullif(ByteSize/power(cast(1024 as bigint),2),0)),
                   'KB' + space(nullif(ByteSize/power(cast(1024 as bigint),1),0)),
                   'Bytes'
                  )
from SizeInStorage
 

Here's the resultset:

ByteSize      Formatted         Converted   Unit
------------  ----------------- ----------- ----
1582690891    1,582,690,891     1.47         GB
786337767     786,337,767       749.91       MB
431093153     431,093,153       411.12       MB
1067677070    1,067,677,070     1,018.22     MB
626880896     626,880,896       597.84       MB
28760         28,760            28.09        KB
308           308               308.00       Bytes

Comment?


~~ CK

Convert Rows into A Delimited String, Part 2

Read the first part of the article, here. Here's another application of how to concatenate rows into a single string column. This script group concatenate the rows based on a PRIMARY KEY (in this case column ID). Here's the sample data that will be summarized:

id          speak_flag  read_flag   ia_flag     lit1_flag   lang
----------- ----------- ----------- ----------- ----------- ----
1           1           1           1           1           fi
1           1           1           0           0           sv
1           1           1           1           0           en
1           1           1           0           1           de
2           1           1           0           0           fi
2           1           0           1           1           sv
2           1           1           0           1           en

Here's the required output

id          speak_lang    read_lang     ia_lang       lit1_lang
----------- ------------- ------------- ------------- ----------
1           de,en,sv,fi   de,en,sv,fi   en,fi         de,fi
2           en,sv,fi      en,fi         sv            en,sv

Here's the code 1. Populate the sample data

create table langdtl
(
id int,
speak_flag int,
read_flag int,
ia_flag int,
lit1_flag int,
lit2_flag int,
lit3_flag int,
lang nvarchar(2)
)

insert into langdtl values(1,1,1,1,1,1,0,'fi')
insert into langdtl values(1,1,1,0,0,0,1,'sv')
insert into langdtl values(1,1,1,1,0,1,0,'en')
insert into langdtl values(1,1,1,0,1,1,1,'de')

insert into langdtl values(2,1,1,0,0,1,1,'fi')
insert into langdtl values(2,1,0,1,1,1,0,'sv')
insert into langdtl values(2,1,1,0,1,0,1,'en')

select 
   id ,speak_flag ,read_flag ,ia_flag ,lit1_flag ,lang 
from langdtl

2. Summarized and Group the Rows

;with language_table
as 
(select
   id, 
   rownum = row_number() over(partition by id order by id),
   speak_lang = isnull(lang + space(nullif(0, speak_flag)) + ',',''),
   read_lang = isnull(lang + space(nullif(0, read_flag))+ ',',''),
   ia_lang = isnull(lang + space(nullif(0, ia_flag))+ ',',''),
   lit1_lang = isnull(lang + space(nullif(0, lit1_flag))+ ',',''),
   lit2_lang = isnull(lang + space(nullif(0, lit2_flag))+ ',',''),
   lit3_lang = isnull(lang + space(nullif(0, lit3_flag))+ ',',''),
   speak_flag, read_flag, ia_flag, lit2_flag, lit3_flag
 from langdtl  
),
sorted_table 
as
(
select TOP 10000000 * from language_table order by id asc, rownum desc
), 
concatenated
as
(  
select 
   id,   
   speak_lang = 
   COALESCE( MIN( CASE WHEN rownum = 4 THEN speak_lang END) , '' )  +
   COALESCE( MIN( CASE WHEN rownum = 3 THEN speak_lang END) , '' ) + 
   COALESCE( MIN( CASE WHEN rownum = 2 THEN speak_lang END) , '' ) +  
   COALESCE( MIN( CASE WHEN rownum = 1 THEN speak_lang END) , '' ),
 
   read_lang = 
   COALESCE( MIN( CASE WHEN rownum = 4 THEN read_lang END) , '' )  +
   COALESCE( MIN( CASE WHEN rownum = 3 THEN read_lang END) , '' ) + 
   COALESCE( MIN( CASE WHEN rownum = 2 THEN read_lang END) , '' ) +  
   COALESCE( MIN( CASE WHEN rownum = 1 THEN read_lang END) , '' ),
 
   ia_lang = 
   COALESCE( MIN( CASE WHEN rownum = 4 THEN ia_lang END) , '' )  +
   COALESCE( MIN( CASE WHEN rownum = 3 THEN ia_lang END) , '' )  +
   COALESCE( MIN( CASE WHEN rownum = 2 THEN ia_lang END) , '' )  +
   COALESCE( MIN( CASE WHEN rownum = 1 THEN ia_lang END) , '' ), 

   lit1_lang = 
   COALESCE( MIN( CASE WHEN rownum = 4 THEN lit1_lang END) , '' ) +
   COALESCE( MIN( CASE WHEN rownum = 3 THEN lit1_lang END) , '' ) +
   COALESCE( MIN( CASE WHEN rownum = 2 THEN lit1_lang END) , '' ) +
   COALESCE( MIN( CASE WHEN rownum = 1 THEN lit1_lang END) , '' ), 

 
   lit2_lang = 
   COALESCE( MIN( CASE WHEN rownum = 4 THEN lit2_lang END) , '' ) +
   COALESCE( MIN( CASE WHEN rownum = 3 THEN lit2_lang END) , '' ) +
   COALESCE( MIN( CASE WHEN rownum = 2 THEN lit2_lang END) , '' ) +
   COALESCE( MIN( CASE WHEN rownum = 1 THEN lit2_lang END) , '' ), 

   lit3_lang = 
   COALESCE( MIN( CASE WHEN rownum = 4 THEN lit3_lang END) , '' ) +
   COALESCE( MIN( CASE WHEN rownum = 3 THEN lit3_lang END) , '' ) +
   COALESCE( MIN( CASE WHEN rownum = 2 THEN lit3_lang END) , '' ) +
   COALESCE( MIN( CASE WHEN rownum = 1 THEN lit3_lang END) , '' ) 
 
from  sorted_table
group by id
)
select id, 
   speak_lang = rtrim(substring(speak_lang,1, len(speak_lang)-1)),
   read_lang = rtrim(substring(read_lang,1, len(read_lang)-1)),
   ia_lang =  rtrim(substring(ia_lang,1, len(ia_lang)-1)),
   lit1_lang = rtrim(substring(lit1_lang,1, len(lit1_lang)-1))
from concatenated   

~~ CK

Convert Column into A Delimited String, Part 1

Here's a problem that I usually encounter on the forum and at work. Sometimes I need a stored proc or function to read a table and return a single string delimited with either comma or tab or bar.
Although SQL Server 2005 included a PIVOT/UNPIVOT relational operators, I think it's still worth it to look at this solution.
Consider this table:

declare @myTable table (rownum int, rowname varchar(10))
declare @DelimitedString varchar(max)

insert @myTable values (1, 'One')
insert @myTable values (2, 'Two')
insert @myTable values (3, 'Three')
insert @myTable values (4, 'Four')
insert @myTable values (5, 'Five')
insert @myTable values (6, 'Six')
insert @myTable values (7, 'Seven')
insert @myTable values (8, 'Eight')
insert @myTable values (9, 'Nine')
insert @myTable values (10, 'Ten')

select * from @mytable

rownum      rowname
----------- ----------
1           One
2           Two
3           Three
4           Four
5           Five
6           Six
7           Seven
8           Eight
9           Nine
10          Ten

I need this output:

One,Two,Three,Four,Five,Six,Seven,Eight,Nine,Ten

So far, this is the best code that I got:

declare @DelimitedString varchar(max)

SELECT
@DelimitedString = COALESCE(@DelimitedString+',' , '') + ColumnName 
FROM @MyTable

SELECT @DelimitedString

Here's the result set:

-------------------------------------------------
One,Two,Three,Four,Five,Six,Seven,Eight,Nine,Ten

Then I tried parsing that string back to rows:

declare @xmldoc int
declare @DelimitedString varchar(250)

set @DelimitedString = 'One,Two,Three,Four,Five,Six,Seven,Eight,Nine,Ten'

set @DelimitedString = '<root><z><y>' + replace(@DelimitedString, ',', '</y></z><z><y>') + '</y></z></root>'

select @DelimitedString

The variable now looks like this:

<root>
  <z>
    <y>One</y>
  </z>
  <z>
    <y>Two</y>
  </z>
  <z>
    <y>Three</y>
  </z>
  <z>
    <y>Four</y>
  </z>
  <z>
    <y>Five</y>
  </z>
  <z>
    <y>Six</y>
  </z>
  <z>
    <y>Seven</y>
  </z>
  <z>
    <y>Eight</y>
  </z>
  <z>
    <y>Nine</y>
  </z>
  <z>
    <y>Ten</y>
  </z>
</root>

Followed by...

exec sp_xml_preparedocument @xmldoc OUTPUT, @DelimitedString

select y as rows
from openxml(@xmldoc, '/root/z',2)
with (y varchar(15))

exec sp_xml_removedocument @xmldoc

Here's the result set:

rows
---------------
One
Two
Three
Four
Five
Six
Seven
Eight
Nine
Ten

As always, comments are welcome


~~ CK