Dynamic PIVOT and cross-tab query (SQL Server)

SQL Server > Operators > PIVOT > Dynamic PIVOT and cross-tab query




Sometimes we don't know values in FOR clause for pivot.
In this example we will build dynamic columns with values and use dynamic query to build pivot and cross-tab query.

Example:

create table pivot_data

(
       id           int,
       Date   date,
       amount int
)
set nocount on
go

insert into
       pivot_data (id, Date, amount)
values
       (1,GETDATE(),100)
insert into
       pivot_data (id, Date, amount)
values
       (1,dateadd(day,1, GETDATE()),200)
insert into
       pivot_data (id, Date, amount)
values
       (2,dateadd(month,1, GETDATE()),300)
insert into
       pivot_data (id, Date, amount)
values
       (2,dateadd(month,2, GETDATE()),400)    

select * from pivot_data

Result:

id            Date                    amount

1             2013-03-04         100
1             2013-03-05         200
2             2013-04-04         300
2             2013-05-04         400

DECLARE

    @cols AS NVARCHAR(MAX),

    @query  AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT  ',' + QUOTENAME(c.Date )
            FROM pivot_data c
            order by Date
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')
print @cols

set @query = 'SELECT id, ' + @cols + ' from

            (
                select
                    id
                    , date
                    , amount
                from
                    pivot_data
           ) x
            pivot
            (
                sum(amount)
                for  date in (' + @cols + ')
            ) p '

execute(@query)

Result:

id 2013-03-04    2013-03-05        2013-04-04        2013-05-04

1 100                 200                     NULL                 NULL
2 NULL              NULL                 300                     400

drop table pivot_data

EXEC() error : could not find stored procedure (SQLServer)

Solution:

EXEC with brackets.

use master;

go

DECLARE

 @sql                nvarchar(max)
set   
  @sql = 'select * from sysdatabases'
-- EXEC without brackets () generate  error
exec   @sql

-- EXEC with brackets success
exec   (@sql )

Print SQL Server Example

SQL Server > Print

Print returns a user-defined message to the client.
The message string can be up to 8000 characters long

Example

declare @p int = 0

while @p < 10
begin
       print 'val ' + cast( @p as nvarchar(10))
    set @p = @p + 1
end

Result:

val 0
val 1
val 2
val 3
val 4
val 5
val 6
val 7
val 8
val 9

@@TRANCOUNT (SQL Server)

SQL Server > Built-In Functions > @@TRANCOUNT

Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.

Example:

PRINT @@TRANCOUNT

BEGIN TRAN --  The BEGIN TRAN statement will increment the  transaction count by 1.
PRINT @@TRANCOUNT
COMMIT     --  The COMMIT statement will decrement the  transaction count by 1.
PRINT @@TRANCOUNT

Result:
0
1
0

SQL query result insert in temp table

SQL Server > Create dynamic temp table

Select into create dynamical temporary table

Example

SELECT

   *
into
   #t
FROM
  table

Call REST (C#)

C# > WebRequestMethods.Http.Get

WebRequestMethods.Http.Get: Represents an HTTP GET protocol method.

The GET method retrieves the information or entity that is identified by the URI of the request.

Example:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApplication3
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            string Uri = "Your Uri";
            String text;
            HttpWebRequest myReq = (HttpWebRequest)WebRequest.Create(Uri);
            myReq.Method = WebRequestMethods.Http.Get;
            myReq.Accept = "application/json";

            HttpWebResponse response = (HttpWebResponse)myReq.GetResponse();
            using (var sr = new StreamReader(response.GetResponseStream()))
            {
                text = sr.ReadToEnd();
            }
        }
    }
}

Location and Size (C#)

C# > Controls > Location and Size

Location

Gets or sets the coordinates of the upper-left corner of the control relative to the upper-left corner of its container.

Size

Gets or sets the height and width of the control.

Example

label1.Location = new Point(4, 302);
label1.Size = new Size(85, 13);