How to convert SQL server result set Rows to Column with variable Comumn and rows

Tag: sql,sql-server,pivot

I am getting this data from into one of view, which internally collect from various sources, I would like to format this data in normalised way, so this can be easily used in SSRS for charting purpose.

Raw Data

ServerName  TimeStamp               CPU_PCT_Utilization
Server_01   2015-04-16 16:23:04.000 2.781995773
Server_01   2015-04-16 16:28:04.000 2.804701567
Server_01   2015-04-16 17:23:04.000 2.804701567
Server_02   2015-04-21 04:33:02.000 1.094034672
Server_02   2015-04-20 17:28:02.000 1.34284699
Server_02   2015-04-20 17:33:02.000 3.027540922
Server_02   2015-04-20 13:28:03.000 0.860414088
Server_02   2015-04-20 13:33:03.000 0.785780609
Server_02   2015-04-20 18:28:02.000 3.027540922
Server_03   2015-04-21 07:04:57.000 1.316953659
Server_03   2015-04-21 07:09:57.000 1.483076811
Server _??  …   .
Server _??  ..  .
Server _??  .   .
Server _??  .   .

Required OUTPUT

TimeStamp   Server_01   Server_02   Server_03   Server _??  Server _??
2015-04-16 16:23:04.000 2.781995773 NULL    NULL    NULL    .
2015-04-16 16:28:04.000 2.804701567 NULL    NULL    .   …
2015-04-16 17:23:04.000 2.804701567 NULL    NULL    …   .
2015-04-21 04:33:02.000 NULL    1.094034672 NULL    .   .
2015-04-20 17:28:02.000 NULL    1.34284699  NULL    .   …
2015-04-20 17:33:02.000 NULL    3.027540922 NULL    …   NULL
2015-04-20 13:28:03.000 NULL    0.860414088 NULL    .   NULL
2015-04-20 13:33:03.000 NULL    0.785780609 NULL    .   NULL
2015-04-20 18:28:02.000 NULL    3.027540922 NULL    …   NULL
2015-04-21 07:04:57.000 NULL    NULL    1.316953659 .   ..
2015-04-21 07:09:57.000 NULL    NULL    1.483076811 .   .
…   .   …   .   NULL    …
..  .   ..  .   .   …
.   …   NULL    …   .   .
.   .   .   .   …   .

I tried using pivot but that didn't helped, if anyone can write a query for this will be great, here rows and columns both are dynamic.

A similar solution is posted at Simple way to transpose columns and rows in Sql? but that with fixed values.

Best How To :

The values to be rotated to columns are not constant, you have to use Dynamic SQL, you need dynamic sql just to build below statement:

case ServerName
   when 'Server_n' then CPU_PCT_Utilization
   else null 
end as Server_n

for building above statement you can select distinct server names in above template into a variable:

declare @CaseStmnt varchar(max)=''
select @CaseStmnt=',case ServerName
when '''+s+''' then CPU_PCT_Utilization
else null 
end as '[email protected]
from (select distinct ServerName s from pvtTbl) q

Below is a demo of what I said:

MS SQL Server Schema Setup:

create table pvtTbl(ServerName varchar(10), dt datetime,CPU_PCT_Utilization float);
go
insert into pvtTbl values
('Server_01',   '2015-04-16 16:23:04.000', 2.781995773),
('Server_01',   '2015-04-16 16:28:04.000', 2.804701567),
('Server_01',   '2015-04-16 17:23:04.000', 2.804701567),
('Server_02',   '2015-04-21 04:33:02.000', 1.094034672),
('Server_02',   '2015-04-20 17:28:02.000', 1.34284699),
('Server_02',   '2015-04-20 17:33:02.000', 3.027540922),
('Server_02',   '2015-04-20 13:28:03.000', 0.860414088),
('Server_02',   '2015-04-20 13:33:03.000', 0.785780609),
('Server_02',   '2015-04-20 18:28:02.000', 3.027540922),
('Server_03',   '2015-04-21 07:04:57.000', 1.316953659),
('Server_03',   '2015-04-21 07:09:57.000', 1.483076811);

Query:

declare @CaseStmnt varchar(max)=''
select @[email protected]+', case ServerName
when '''+s+''' then CPU_PCT_Utilization
else null 
end as '+s
from (select distinct ServerName s from pvtTbl) q 
exec('select dt '[email protected]+' from pvtTbl')

Results:

|                      dt |   Server_01 |   Server_02 |   Server_03 |
|-------------------------|-------------|-------------|-------------|
| April, 16 2015 16:23:04 | 2.781995773 |      (null) |      (null) |
| April, 16 2015 16:28:04 | 2.804701567 |      (null) |      (null) |
| April, 16 2015 17:23:04 | 2.804701567 |      (null) |      (null) |
| April, 21 2015 04:33:02 |      (null) | 1.094034672 |      (null) |
| April, 20 2015 17:28:02 |      (null) |  1.34284699 |      (null) |
| April, 20 2015 17:33:02 |      (null) | 3.027540922 |      (null) |
| April, 20 2015 13:28:03 |      (null) | 0.860414088 |      (null) |
| April, 20 2015 13:33:03 |      (null) | 0.785780609 |      (null) |
| April, 20 2015 18:28:02 |      (null) | 3.027540922 |      (null) |
| April, 21 2015 07:04:57 |      (null) |      (null) | 1.316953659 |
| April, 21 2015 07:09:57 |      (null) |      (null) | 1.483076811 |

Setting time limit in SQL Query

sql-server

Assuming that you want between 10:00 AM and 5:00 PM, you can use this SELECT CASE WHEN CAST(GETDATE() AS TIME) BETWEEN '10:00:00' AND '17:00:00' THEN 1 ELSE 0 END In this context, Select * from table makes no sense, unless you have a time column and want to evaluate that....

SQL Server / C# : Filter for System.Date - results only entries at 00:00:00

c#,asp.net,sql-server,date,gridview-sorting

What happens if you change all of the filters to use 'LIKE': if (DropDownList1.SelectedValue.ToString().Equals("Start")) { FilterExpression = string.Format("Start LIKE '{0}%'", TextBox1.Text); } Then, you're not matching against an exact date (at midnight), but matching any date-times which start with that date. Update Or perhaps you could try this... if (DropDownList1.SelectedValue.ToString().Equals("Start"))...

mysql_real_escape_string creates \ in server only not in local

php,sql

Your server has magic quotes enabled and your local server not. Remove it with the following sentence set_magic_quotes_runtime(0) As this function is deprecated and it will be deleted in PHP 7.0, I recommend you to change your php.ini with the following sentencies: magic_quotes_gpc = Off magic_quotes_runtime = Off If you...

How to use OFFSET and Fetch without Order by in SQL Server

sql-server,sql-server-2012,sql-order-by,fetch,offset

By adding an identity column to the temp table variable declare @TempTable table([some columns], rownr int identity(1,1) ) INSERT INTO @TempTable [some columns] select [some columns] from table1 order by col1 INSERT INTO @TempTable [same columns] select [some columns] from table2 order by col2 An automatic incrementing number is added...

Pull information from SQL database and getting login errors

php,sql,database

change $username = "'rylshiel_order"; to $username = "rylshiel_order"; and you should be through. You are passing on an extra single quote here. ...

Add 1 to datediff result if time pass 14:30 or 2:30 PM

sql,ms-access,ms-access-2007

Could be: SELECT reservations.customerid, DateDiff("d",reservations.checkin_date, Date()) + Abs(DateDiff("s", #14:30#, Time()) > 0)AS Due_nights FROM reservations ...

Implement reference key in SQL Server

sql-server,sql-server-2008

It's called a "one-to-zero-or-one" relationship, as one Line might be associated to zero or one TestPacks. You can implement it by using a FK that allows NULL values. CREATE TABLE TestPack (id INT, PRIMARY KEY (id)) CREATE TABLE Line (id INT, TestPackId INT NULL, FOREIGN KEY (TestPackId) REFERENCES TestPack(id)) By...

Take thousand value in SQL

sql,sql-server

SELECT CONVERT(INT,YourColumn) % 1000 FROM dbo.YourTable ...

SQL Multiple LIKE Statements

sql,sql-server,tsql,variables,like

WITH CTE AS ( SELECT VALUE FROM ( VALUES ('B79'), ('BB1'), ('BB10'), ('BB11'), ('BB12'), ('BB18'), ('BB2'), ('BB3'), ('BB4'), ('BB5'), ('BB6'), ('BB8'), ('BB9'), ('BB94'), ('BD1'), ('BD10'), ('BD11'), ('BD12'), ('BD13'), ('BD14'), ('BD15'), ('BD16'), ('BD17'), ('BD18'), ('BD19'), ('BD2'), ('BD20'), ('BD21'), ('BD22'), ('BD3'), ('BD4'), ('BD5'), ('BD6') ) V(VALUE) ) SELECT * FROM tbl_ClientFile...

Cannot Browse in sql to backup bak file

sql-server

Try this query RESTORE DATABASE Databasename FROM DISK = 'Z:\SQLServerBackups\tms.bak' ; ...

The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument

sql,sql-server,sql-server-2008

You could use CTE to define your null values and then pivot the data something like this: ;WITH t AS ( SELECT isnull(jan, 0) AS jan ,isnull(feb, 0) AS feb ,sum(data) AS amount FROM your_table --change this to match your table name GROUP BY jan,feb ) SELECT * FROM (...

Get unique row by single column where duplicates exist

sql,sql-server

SELECT MIN(date),thread_id FROM messages GROUP BY thread_id HAVING COUNT(thread_id) > 1 ...

T-SQL Ordering a Recursive Query - Parent/Child Structure

sql,tsql,recursion,order,hierarchy

The easiest way would be to pad the keys to a fixed length. e.g. 038,007 will be ordered before 038,012 But the padding length would have to be safe for the largest taskid. Although you could keep your path trimmed for readability and create an extra padded field for sorting....

Matplotlib: Plot the result of an SQL query

python,sql,matplotlib,plot

Take this for a starter code : import numpy as np import matplotlib.pyplot as plt from sqlalchemy import create_engine import _mssql fig = plt.figure() ax = fig.add_subplot(111) engine = create_engine('mssql+pymssql://**:****@127.0.0.1:1433/AffectV_Test') connection = engine.connect() result = connection.execute('SELECT Campaign_id, SUM(Count) AS Total_Count FROM Impressions GROUP BY Campaign_id') ## the data data =...

SQL: overcoming no ORDER BY in nested query

sql,sqlite

Use a join instead: SELECT a, b FROM t JOIN (SELECT DISTINCT date FROM t ORDER BY date DESC LIMIT 2) tt on t.date = tt.date; ...

Convert AWK command to sqlite query

sql,awk,sqlite3

SQLite is an embedded database, i.e., it is designed to be used together with a 'real' programming language. It might be possible to import that log file into a database file, but the whole point of having a database is to store the data, which is neither a direct goal...

ONLY display certain rows from an inner joined table using a certain colum as a parameter from one of the inner joined tables

sql-server,join

If I understood correctly this should be what you're looking for SELECT A.UserName, A.[Email ID], A.[Supervisor Email ID] FROM A INNER JOIN B ON A.UserCode = B.UserCode WHERE B.ACTIVE_FLAG = 'Y' ...

oracle sql error Case When Then Else

sql,oracle,oracle11g

Perhaps this is what you want? If there are rows in SecondTable, then do the second EXISTS: SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND (NOT EXISTS (SELECT 1 from SecondTable) OR EXISTS (SELECT 1 FROM SecondTable WHERE FirstTable.Key = SecondTable.Key and SecondTable.RowProcessed = 'Y')) AND OtherConditions ...

left join table, find both null and match value

sql,sql-server,join

Try FULL OUTER JOIN. This is the sqlfiddle. It will produce the op you are expecting SQLFiddle select t1.years, t1.numOfppl, t2.years, t2.numOfppl from t1 full outer join t2 on t1.years=t2.years ...

Select Statement on Two different views

sql

Yes, You can use two different view in SELECT query. You have to JOIN them, if them have matched column in each other. Just treat two different views as like two different tables when using in SELECT Clause. SELECT vw1.a, vw2.b FROM View1 vw1 INNER JOIN View2 vw2 ON vw1.id...

TSQL - Error in stored procedure due to conversion failure

sql-server,sql-server-2008,tsql

I'd suggest doing it like that: SET @DATE_RELEASE_START = '2015-01-01'; SET @DATE_RELEASE_END = '2015-05-31' SELECT @statement = ' SELECT * FROM (SELECT AFCDENTE, M.ID_MODIFICATION_CODE, COUNT(*) AS Conteggio--, CAST((COUNT(*) * 100/ 15032) AS decimal(10,7)) AS Percentage FROM CIC_LOG_MODIFICHE AS L INNER JOIN ADM_MODIFICATION_CODE AS M ON L.CD_MODIFICATION_CODE = M.CD_MODIFICATION_CODE INNER JOIN...

Join SQL query Results and Get-ChildItem Results

sql-server,sql-server-2008,powershell

OK so if the SQL query does not have results then NULL is returned and, in essence, nothing is added to the $dbResults array. Instead lets append the results to a custom object. I don't know what PowerShell version you have so I needed to do something that I know...

MySQL: Select several rows based on several keys on a given column

mysql,sql,database

If you are looking to find the records matching with both the criteria here is a way of doing it select `item_id` FROM `item_meta` where ( `meta_key` = 'category' and `meta_value` = 'Bungalow' ) or ( `meta_key` = 'location' AND `meta_value` = 'Lagos' ) group by `item_id` having count(*)=2 ...

Sql injected code is inserted to my database . How to remove it

sql-server

You can use the fact that html code starts with symbol <. Then: UPDATE TableName SET SomeColumn = CASE WHEN CHARINDEX('<', SomeColumn) > 0 THEN SUBSTRING(SomeColumn, 1, CHARINDEX('<', SomeColumn) - 1) ELSE SomeColumn END If this is not true then we will need more information about data. May be it...

Query how often an event occurred at a given time

mysql,sql

This could be done using user defined variable which is faster as already mentioned in the previous answer. This needs creating incremental variable for each group depending on some ordering. And from the given data set its user and date. Here how you can achieve it select user, date, purchase_count...

INSERT INTO fails due to incorrect conversion T-SQL

sql-server,tsql

The problem is that there's no implicit conversion from varchar (your literal) to sql_variant. Just add an explicit conversion and you're done: cast('FooBar' as sql_variant) ...

Title search in SQL With replacement of noice words [on hold]

sql,sql-server,sql-server-2008

I think you want something like this: DECLARE @nw TABLE ( sn INT, [key] VARCHAR(100) ) INSERT INTO @nw VALUES ( 1, 'and' ), ( 2, 'on' ), ( 3, 'of' ), ( 4, 'the' ), ( 5, 'view' ) DECLARE @s VARCHAR(100) = 'view This of is the Man';...

SQL Customized search with special characters

sql,sql-server,sql-server-2008

Here is my attempt using Jeff Moden's DelimitedSplit8k to split the comma-separated values. First, here is the splitter function (check the article for updates of the script): CREATE FUNCTION [dbo].[DelimitedSplit8K]( @pString VARCHAR(8000), @pDelimiter CHAR(1) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH E1(N) AS ( SELECT 1 UNION ALL SELECT...

Foreign key in C#

c#,sql,sql-server,database

You want create relationship in two table Refer this link http://www.c-sharpcorner.com/Blogs/5608/create-a-relationship-between-two-dataset-tables.aspx...

Default the year based on month value

sql,sql-server

SQL Server is correct in what it's doing as you are requesting an additional row to be returned which if ran now 2015-06-22 would return "2016" Your distinct only works on the first select you've done so these are your options: 1) Use cte's with distincts with subq1 (syear, eyear,...

Using Sum in If in Mysql

mysql,sql,select,sum

Using least would be much easier: SELECT LEAST(SUM(my_field), 86400) FROM my_table ...

How to select next row after select in SQL Server?

sql,sql-server

The query can be written as: ; WITH Base AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY Shift_Date) RN FROM #Table1 ) , WithC AS ( SELECT * FROM Base WHERE Shift2 = 'C' ) SELECT * FROM WithC UNION SELECT WithCNext.* FROM WithC C LEFT JOIN Base WithCNext ON...

Retrieve Values As Column

mysql,sql

If types are fixed (just IMPRESSION and CLICK), you could use a query like this: SELECT headline, SUM(tracking_type='IMPRESSION') AS impressions, SUM(tracking_type='CLICK') AS clicks FROM tracking GROUP BY headline ...

Fastest way to add a grouping column which divides the result per 4 rows

sql,sql-server,tsql,sql-server-2012

Try this: SELECT col, (ROW_NUMBER() OVER (ORDER BY col) - 1) / 4 + 1 AS grp FROM mytable grp is equal to 1 for the first four rows, equal to 2 for the next four, equal to 3 for the next four, etc. Demo here Alternatively, the following can...

SQL Group By multiple categories

php,mysql,sql,mysqli

Just include a case statement for the group by expression: SELECT (CASE WHEN Categories.name like 'Cat3%' THEN 'Cat3' ELSE Categories.name END) as name, sum(locations.name = 'loc 1' ) as Location1, sum(locations.name = 'loc 2') as Location2, sum(locations.name = 'loc 3') as Location3, count(*) as total FROM ... GROUP BY (CASE...

Error connecting to MSSQL using PHP

php,sql-server,pdo,odbc,sqlsrv

Change it to: $this->link = new PDO( "sqlsrv:Server={$this->serverName},{$this->port};Database={$this->db};", $this->uid, $this->pwd ); The default SQL Server port is 1433. Note the curly brackets, they allow for class variables....

SQL varchar variable inserts question mark

sql,sql-server-2012

there is un recognizable character in your string that is giving that ?. Delete the value and retype. see my above screen shot...

Can someone explain to me how this statement is an exclude?

sql,sql-server,tsql

I can explain... a query that's very close to yours. Let me alter it to: SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON (t1.ColumnX = t2.ColumnX AND t2.columnY = 1) WHERE t2.tableID IS NULL This query retrieves all rows from t1, then checks to see if...