SQL SERVER PIVOT TABLE

create table DailyIncome
(
VendorId nvarchar(10),
IncomeDay nvarchar(10),
IncomeAmount int
 )

insert into DailyIncome values ('SPIKE', 'FRI', 100)
insert into DailyIncome values ('SPIKE', 'MON', 300)
insert into DailyIncome values ('FREDS', 'SUN', 400)
insert into DailyIncome values ('SPIKE', 'WED', 500)
insert into DailyIncome values ('SPIKE', 'TUE', 200)
insert into DailyIncome values ('JOHNS', 'WED', 900)
insert into DailyIncome values ('SPIKE', 'FRI', 100)
insert into DailyIncome values ('JOHNS', 'MON', 300)
insert into DailyIncome values ('SPIKE', 'SUN', 400)
insert into DailyIncome values ('JOHNS', 'FRI', 300)
insert into DailyIncome values ('FREDS', 'TUE', 500)
insert into DailyIncome values ('FREDS', 'TUE', 200)
insert into DailyIncome values ('SPIKE', 'MON', 900)
insert into DailyIncome values ('FREDS', 'FRI', 900)
insert into DailyIncome values ('FREDS', 'MON', 500)
insert into DailyIncome values ('JOHNS', 'SUN', 600)
insert into DailyIncome values ('SPIKE', 'FRI', 300)
insert into DailyIncome values ('SPIKE', 'WED', 500)
insert into DailyIncome values ('SPIKE', 'FRI', 300)
insert into DailyIncome values ('JOHNS', 'THU', 800)
insert into DailyIncome values ('JOHNS', 'SAT', 800)
insert into DailyIncome values ('SPIKE', 'TUE', 100)
insert into DailyIncome values ('SPIKE', 'THU', 300)
insert into DailyIncome values ('FREDS', 'WED', 500)
insert into DailyIncome values ('SPIKE', 'SAT', 100)
insert into DailyIncome values ('FREDS', 'SAT', 500)
insert into DailyIncome values ('FREDS', 'THU', 800)
insert into DailyIncome values ('JOHNS', 'TUE', 600)
SELECT * FROM DailyIncome
select * from DailyIncome
pivot (avg (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as AvgIncomePerDay
select * from DailyIncome
pivot (max (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as MaxIncomePerDay

select * from DailyIncome
pivot (min (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as MaxIncomePerDay
select * from DailyIncome
pivot (sum (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as MaxIncomePerDay

drop table DailyIncome

 

WEB API

http://www.asp.net/web-api/overview/creating-web-apis/using-web-api-with-entity-framework/using-web-api-with-entity-framework,-part-1

insert multiple rows sql server 2008

For inserting multiple rows using one insert statement you can do as following example :

declare @dept table
 (
   DeptId int identity(1,1) not null,
   DeptName nvarchar(500)
 )

 insert into @dept values ('sales'),('marketting'),('hr'),('IT')

 select * from @dept 

SQL fiddle can be check here  

Get random rows from a table in mysql

if you want to get random rows from your table  so you can use rand() function of my sql in order by clause. 

so for using this you have to use this in order by clause. 

SELECT id,title,publishDate FROM mytable ORDER BY RAND()  LIMIT 5

but how its works ? how rand() function generates rows randomly . 

actually rand() function generates  a random floating-point value v in the range 0 <= v < 1.0. If a constant integer argument N is specified, it is used as the seed value, which produces a repeatable sequence of column values

SHOW SERIAL NUMBER IN RDLC REPORT

may be you need to show serial number in your rdlc report then you can use following syntax for showing 1,2,3... continuous on in table field

=RowNumber(Nothing)

and if you want to re-generate serial number for each  group in rdlc then you can use following syntax

   =RowNumber("table1_Group1")

where table1_Group1 is group name . 

Thanks 

dynamic PIVOT where number of columns are dynamic in pivot columns

A PIVOT used to rotate the data from one column into multiple columns.

STATIC Pivot meaning you hard code the columns that you want to rotate

For  example :

CREATE TABLE temp
(
    period_id INTEGER ,
    lease_id  INTEGER  ,
    charge_id VARCHAR(20) ,
    charge_amount MONEY
)
INSERT INTO temp
        ( period_id ,
          lease_id ,
          charge_id ,
          charge_amount
        )
VALUES  ( 100 , -- period_id - integer
          2000 , -- lease_id - integer
          '300' , -- charge_id - varchar(20)
          12345  -- charge_amount - money
        ) ,
        ( 101 , -- period_id - integer
          2000 , -- lease_id - integer
          '300' , -- charge_id - varchar(20)
          678910  -- charge_amount - money
        ) ,
        ( 101 , -- period_id - integer
          2002 , -- lease_id - integer
          '300' , -- charge_id - varchar(20)
          78950  -- charge_amount - money
        ) ,
        ( 101 , -- period_id - integer
          2002 , -- lease_id - integer
          '310' , -- charge_id - varchar(20)
          9002  -- charge_amount - money
        )

        Select period_id,lease_id,[300] as charge_300, [310] as Charge_310  FROM(Select period_id,lease_id,charge_id,charge_amount from temp )p     pivot(sum(charge_amount)FOR charge_id in ([300],[310])) as PVT

but in case of number of columns change dynamically then you need dynamic pivot . for this you need to create series of columns using dynamic sql . 

DECLARE @columns VARCHAR(8000)

SELECT 
@columns = 
COALESCE
(
 @columns + ',[' + charge_id + ']',
 '[' + charge_id + ']'
)
FROM 
#temp 
group by charge_id

// The above query tries to create distinct charge_id as the columns
// @columns= [300],[301]

DECLARE @query VARCHAR(8000)
SET @query = 'SELECT *
FROM
(
 SELECT 
 period_id,lease_id ,charge_id,charge_amount 
 FROM 
 #temp 
) P
PIVOT
(
 SUM(charge_amount) 
 FOR charge_id in (' + @columns + ')
) AS PVT'

 EXECUTE (@query)
 GO

Links for above sql fiddle 

function to use comma seprated values in to a table so that it can be use in IN statement in sql

function to use comma seprated values in to a table so that it can be use in IN statement in sql

ALTER FUNCTION [dbo].[fnNTextToIntTable] (@Data nvarchar(4000))
RETURNS
    @IntTable TABLE ([Value] NVARCHAR(500) NULL)
AS
BEGIN
    SET @Data=Replace(@Data,'$',',')
    DECLARE @Ptr int, @Length int, @v nchar, @vv nvarchar(10)
    SELECT @Length = (DATALENGTH(@Data) / 2) + 1, @Ptr = 1
    WHILE (@Ptr < @Length)
    BEGIN
        SET @v = SUBSTRING(@Data, @Ptr, 1)
        IF @v = ','
        BEGIN
            INSERT INTO @IntTable (Value) VALUES (CAST(RTRIM(LTRIM(@vv)) AS NVARCHAR(500)))
            SET @vv = NULL
        END
        ELSE
        BEGIN
            SET @vv = ISNULL(RTRIM(LTRIM(@vv)), '') + @v
        END
        SET @Ptr = @Ptr + 1
    END
    -- If the last number was not followed by a comma, add it to the result set
    IF @vv IS NOT NULL
        INSERT INTO @IntTable (Value) VALUES (CAST(RTRIM(LTRIM(@vv)) AS NVARCHAR(500)))
    RETURN
END