SQL Query

Question

Hello,

I want to write a sql query.In my case table is having data as below

variable_int_col   timestamp_int_col   value_col

8                         1449486435            25

8                         1449486436            26

9                         1449486435            40

9                         1449486436            44

9                         1449486440            52

my query should insert data into table having colmuns

Timestamp_int_col                  Variable_8_value              variable_9_value

1449486435                      25                                          40

1449486436                 26                              44

1449486440                  0                               52

Help please me with this query

Answer 1

insert into tbl2 (
  Timestamp_int_col
  , Variable_8_value
  , variable_9_value
)
select
pvt.timestamp_int_col
  , [8]
  , [9]
from tbl1 
pivot
(Sum(value_col) 
FOR variable_int_col in ([8],[9])) as pvt

https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspxi

---

Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 2

But what if i don't know the value of variable_int_col.So how can i write in query

My second table is having variable_int_col values.can i fetch from there and use that in this query

Answer 3

SELECT timestamp_int_col,
SUM(CASE WHEN variable_int_col = 8 THEN value_col END) AS Variable_8_value,
SUM(CASE WHEN variable_int_col = 9 THEN value_col END) AS Variable_9_value
FROM table
GROUP BY timestamp_int_col

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 4

But what if i don't know the value of variable_int_col.So how can i write in query

It depends how flexible you want this to be, if variable_int_col could be literally anything you'll probably need to use dynamic sql to build up the correct query string and then execute it.

If it will be a fixed ranged then, as Visakh has suggested, simply create a pivot for each potential value

Answer 5

But what if i don't know the value of variable_int_col.So how can i write in query

My second table is having variable_int_col values.can i fetch from there and use that in this query

You can fetch it from another table and use it

In which case you would require creating query dynamically

see

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 6

Can you please show how can i write dynamic query for fetching data from another table and using in the existing above mentioned query

Answer 7

Hi Tech Aspirant,

Script below using dynamic pivot is for your reference.

If object_ID(N'tempdb.dbo.#tmp') is not NULL
    Drop table #tmp;

create table #tmp
(
variable_int_col int,
timestamp_int_col int,
value_col int
)

insert into #tmp values
(8,1449486435,25),
(8,1449486436,26),
(9,1449486435,40),
(9,1449486436,44),
(9,1449486440,52)

select * from #tmp

Declare @sql varchar(max);
Declare @sql2 varchar(max);

set @sql= STUFF((select distinct ','+ QUOTENAME(variable_int_col) from #tmp FOR XML PATH('')) ,1,1,'');
set @sql2= STUFF((select distinct ','+ 'ISNULL(' + QUOTENAME(variable_int_col) + ',0)' + ' AS ' + QUOTENAME('variable_' + cast(variable_int_col as varchar) + '_value') from #tmp FOR XML PATH('')) ,1,1,'');

set @sql='select timestamp_int_col,' + @sql2 + ' from #tmp t  
               PIVOT (SUM(value_col) FOR variable_int_col IN ('+@sql+')) p';
-- print @sql

If object_ID(N'dbo.newTable') is not NULL
    Drop table newTable;

set @sql='select * into newTable from (' + @sql + ') s';

exec(@sql);

select * from newTable;

---

Sam Zha
TechNet Community Support

Answer 8

Can you please show how can i write dynamic query for fetching data from another table and using in the existing above mentioned query

like this

DECLARE @varColList varchar(max)

SELECT @varColList = STUFF((SELECT DISTINCT ',[' + CAST(var_col_list AS varchar(10)) + ']'
FROM Table2 
ORDER BY ',[' + CAST(var_col_list AS varchar(10)) + ']' 
FOR XML PATH('')),1,1,'')


DECLARE @SQL = 'SELECT *
FROM Table1 t
PIVOT(SUM(value_col) FOR variable_int_col IN (' + @varColList + '))p'

EXEC (@SQL)

Table1 is main table

Table2 is the other table where you've int values

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 9

Its working but my timstamp is in UTC integer format and i want to convert it into Local Time below is my actual query but is resulting into error

select * from ARCHIEVE_EXPORT_DB_AR

Declare @sql varchar(max);
Declare @sql2 varchar(max);

set @sql= STUFF((select distinct ','+ QUOTENAME(VARIABLE) from ARCHIEVE_EXPORT_DB_AR FOR XML PATH('')) ,1,1,'');
set @sql2= STUFF((select distinct ','+ 'ISNULL(' + QUOTENAME(VARIABLE) + ',0)' + ' AS ' + QUOTENAME('variable_' + cast(VARIABLE as varchar) + '_value') from ARCHIEVE_EXPORT_DB_AR FOR XML PATH('')) ,1,1,'');

set @sql='select dateAdd(hour, 
		datediff(hour, getutcdate(), getdate()), --UTC offset
		dateadd(second,ARCHIEVE_EXPORT_DB_AR.TIMESTAMP_S, ' + '1/1/1970 12:00 AM + ')),' + @sql2 + ' from ARCHIEVE_EXPORT_DB_AR t  
               PIVOT (SUM(VALUE) FOR VARIABLE IN ('+@sql+')) p';
-- print @sql

If object_ID(N'dbo.newTable') is not NULL
    Drop table newTable;

set @sql='select * into newTable from (' + @sql + ') s';

exec(@sql);

select * from newTable;

Answer 10

Hi Tech Aspirant,

According to your description and script above, modify part of your code as follows. By the way, “--UTC offset” inside a string will not be parsed as comments.

set @sql='select dateAdd(hour, 
             datediff(hour, getutcdate(), getdate()), 
             dateadd(second,TIMESTAMP_S, ''1/1/1970 12:00 AM'')) AS local_time,' + @sql2 + ' from ARCHIEVE_EXPORT_DB_AR t  
               PIVOT (SUM(VALUE) FOR VARIABLE IN ('+@sql+')) p';

---

Sam Zha
TechNet Community Support