Al Programming

(
       id                  int,
       name         nvarchar(max)
)
go
create table [order]
(
       id                  int,
       Date         date,
       amount       int,
       customer_id int
)
set nocount on
go

insert into
       customer (id, name)
values
       (1,'Customer 1')
insert into
       customer (id, name)
values
       (2,'Customer 2')
insert into
      [order] (id, Date, amount, customer_id)
values
       (1,GETDATE(),100, 1)
insert into
       [order] (id, Date, amount, customer_id)
values
       (2,GETDATE(),100, 1)
insert into
       [order] (id, Date, amount, customer_id)
values
       (3,GETDATE(),200, 1)
insert into
       [order] (id, Date, amount, customer_id)
values
       (4,GETDATE(),300, 2)

--query returns results as a rowset

select

       customer.id,
       customer.name,
       ord.id order_id,
       ord.Date,
       ord.amount
from
       customer
       join [order] ord on customer.id = ord.customer_id

Result:

id name order_id Date amount

1             Customer 1        1               2013-03-04         100
1             Customer 1        2               2013-03-04         100
1             Customer 1        3               2013-03-04         200
2             Customer 2        4               2013-03-04         300


--query returns results as XML

AUTO mode returns query results as nested XML elements

select

       customer.id,
       customer.name,
       ord.id order_id,
       ord.Date,
       ord.amount
from
       customer
       join [order] ord on customer.id = ord.customer_id
FOR XML auto

Result:

RAW mode transforms each row in the query result set into an XML element that has the generic identifier , or the optionally provided element name.

select

       customer.id,
       customer.name,
       ord.id order_id,
       ord.Date,
       ord.amount
from
       customer
       join [order] ord on customer.id = ord.customer_id
FOR XML raw

Result:

PATH mode provides a simpler way to mix elements and attributes.

select

       customer.id,
       customer.name,
       ord.id order_id,
       ord.Date,
       ord.amount
from
       customer
       join [order] ord on customer.id = ord.customer_id
FOR XML path

Result:

<row>
<id>1</id>
<name>Customer 1</name>
<order_id>1</order_id>
<Date>2013-03-04</Date>
<amount>100</amount>
</row>
<row>
<id>1</id>
<name>Customer 1</name>
<order_id>2</order_id>
<Date>2013-03-04</Date>
<amount>100</amount>
</row>
<row>
<id>1</id>
<name>Customer 1</name>
<order_id>3</order_id>
<Date>2013-03-04</Date>
<amount>200</amount>
</row>
<row>
<id>2</id>
<name>Customer 2</name>
<order_id>4</order_id>
<Date>2013-03-04</Date>
<amount>300</amount>
</row>

drop table [order]

drop table customer

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

Friday, March 1, 2013

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

Solution:

EXEC with brackets.

use master;

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

Search This Blog

Tuesday, March 5, 2013

Monday, March 4, 2013

Friday, March 1, 2013