SQL SERVER – PIVOT Table Example

This is quite a popular question and I have never wrote about this on my blog. A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column names, effectively rotating a table.
USE AdventureWorks
GO
SELECT [CA], [AZ], [TX]
FROM
(
SELECT sp.StateProvinceCode
FROM Person.Address a
INNER JOIN Person.StateProvince sp
ON a.StateProvinceID = sp.StateProvinceID
) p
PIVOT
(
COUNT (StateProvinceCode)
FOR StateProvinceCode
IN ([CA], [AZ], [TX])
)
AS pvt;


Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

76 thoughts on “SQL SERVER – PIVOT Table Example

  1. Nice, but we have too many cases with PIVOT, can you explain in general some 4-5 situation ( don’t recommend Books Online plz everyone we know about it)!

    the table example:

    IDj
    YEAR
    OFFICE
    ITEMS
    PRICE
    QTY
    COMMENTS

    and now the data should look like this:

    OFFICE = ROWS
    ITEMS = COLUMNS
    SUM(QTY) = DETAILS
    and
    GROUPING BY YEAR OR FILTERING BY YEAR

    thnx!

  2. Pingback: SQL SERVER - UNPIVOT Table Example Journey to SQL Authority with Pinal Dave

  3. hi Pinal I understood the meaning of pivot query
    but if i have around 100 of departments and i want to use pivot query for this to find out the avg sal departmentwise. so what to to for this ?

  4. Is it possible to use two aggreation in the PIVOT? If possible, can you please suggestion how to use it?

    e.g

    SUM(Price), Count(Product)
    FOR ProductId IN ( [1],[2],[3] )

    Thank and regards,
    Robin.

  5. Forgot one thing that. I did by using JOIN statement. It is working but I would like to know better way.

    Thank for your help.

    Regards,
    Robin

  6. Pingback: SQL SERVER - Example of PIVOT UNPIVOT Cross Tab Query in Different SQL Server Versions Journey to SQL Authority with Pinal Dave

  7. How to PIVOT table with date column?

    Source table:
    ==========

    SiteNo SubNo MV Date
    —— ——– — ————
    001 SUB-028 V1 2007-03-11
    001 SUB-028 V2 2007-05-21
    001 SUB-028 V3 2007-06-22
    001 SUB-028 V4 2007-07-11
    001 SUB-028 V5 2007-08-05
    001 SUB-028 V6 2007-09-28
    002 SUB-010 V1 2007-04-22
    002 SUB-010 V2 2007-05-22
    002 SUB-010 V3 2007-06-05

    Result table:
    ===========
    SiteNo SubNo [V1] [V2] [V3] [V4] [V5] [V6]
    —— ——— ———– ——— ———- ———– ———– ———-
    001 SUB-028 2007-03-11 2007-05-21 2007-06-22 2007-07-11 2007-08-05 2007-09-28
    002 SUB-010 2007-04-22 2007-05-22 2007-06-05

  8. @Aaron.

    It took nearly one and half hours for me to write these 3 lines script. The main hurdle came at usage of aggregate functions used in Pivots.

    Because I have to use a function with Pivot, there was no way I can display date values in V1. V2 …. V6 columns.

    I have used Max function. Since you have only one date for one combination of V1 and SiteNo, this will work fine in your case, but in general this could be misleading…. as we are asking database engine to pick up max values … anyways here is your query.

    SELECT SiteNo, subno ,[V1] ,[V2] ,[V3] ,[V4] ,[V5] ,[V6]
    FROM (
    SELECT SiteNo, subno, MV, Date
    FROM SourceTable) up
    PIVOT ( max(Date) FOR MV in (V1 ,V2 ,V3 ,V4 ,V5 ,V6 ) )AS pvt
    ORDER BY SiteNo

    I have tested Output for this script , it does matches with your expected Output.

    Regards,
    IM

  9. Hi *Imran Mohammed*, it’s so great you worked it right. I was struggling on what function should be used too. Yes, in the case I posted, it works perfectly.
    As you already see that actually I do have case like below, which for one MV value there’re more than one dates. I wonder if this can be done by PIVOT. If not, any other option?

    SiteNo SubNo MV Date
    —— ——– — ————
    001 SUB-028 V1 2007-03-11
    001 SUB-028 V1 2007-03-15
    001 SUB-028 V1 2007-03-19
    001 SUB-028 V2 2007-05-21
    001 SUB-028 V3 2007-06-22
    001 SUB-028 V4 2007-07-11
    001 SUB-028 V4 2007-07-17
    001 SUB-028 V4 2007-07-21
    001 SUB-028 V4 2007-07-29
    001 SUB-028 V5 2007-08-05
    001 SUB-028 V6 2007-09-28
    002 SUB-010 V1 2007-04-22
    002 SUB-010 V2 2007-05-22
    002 SUB-010 V3 2007-06-05
    002 SUB-010 V3 2007-07-05

  10. (continue last post)

    The challenge is that MV value could be any string, could be V1 ~ Vx. And for Same MV value, they just want to list all dates, like

    SiteNo SubNo [V1] [V1] [V1] [V2] [V3] [V4] [V5] [V6]
    —— ——— ———– ——— ———- ———– ———– ———- ———— —————-
    001 SUB-028 2007-03-11 2007-03-15 2007-03-19 2007-05-21 2007-06-22 2007-07-11 2007-08-05 2007-09-28

  11. Pingback: SQL SERVER - PIVOT and UNPIVOT Table Examples Journey to SQL Authority with Pinal Dave

  12. Hi PLS HELP ME, i have a table like that

    d a b c a1 b2 c3
    401 0.3 0.8 0.9 z x y
    402 0.4 0.5 0.6 x z y

    And i need this result in my query
    d z x y
    401 0.3 0.8 0.9
    402 0.5 0.4 0.6

    I was thinking on pivot tables but i dont know how to do it with more colums

  13. @xomaly i do not understand your question. So, i guessed. How about this:

    WITH
    Data
    AS
    (
    SELECT 401 d, 0.3 a, 0.8 b, 0.9 c, ‘z’ a1, ‘x’ b2, ‘y’ c3 UNION ALL
    SELECT 402 d, 0.4 a, 0.5 b, 0.6 c, ‘x’ a1, ‘z’ b2, ‘y’ c3
    )
    SELECT
    d,
    CASE a1
    WHEN ‘z’ THEN a
    WHEN ‘X’ THEN b
    WHEN ‘y’ THEN c
    END,
    CASE b2
    WHEN ‘z’ THEN a
    WHEN ‘X’ THEN b
    WHEN ‘y’ THEN c
    END,
    CASE c3
    WHEN ‘z’ THEN a
    WHEN ‘X’ THEN b
    WHEN ‘y’ THEN c
    END
    FROM
    Data;

  14. hi..i ve written one query it return the following results

    date [name]
    06:00 aaaa
    06:00 bbbb
    06:00 ccccc
    06:30 dddd
    06:30 eeee
    06:30 ffffffff
    06:30 gggg
    07:00 hhhh
    08:00 NULL
    08:30 NULL
    09:00 NULL
    09:30 IIIIII
    10:00 NULL
    .
    .
    ..
    i want to display this in the following format

    date name
    06:00 aaaa bbbb cccc NULL
    06:30 dddd eeee ffffff gggg
    07:00 hhhh NULL NULL NULL
    08:30 NULL NULL NULL NULL
    09:00 NULL NULL NULL NULL
    10:00 IIIIII NULL NULL NULL
    .
    .
    .
    .
    is it possible using pivot? kindly help me

  15. Hi Pinal,
    You’re examples on pivot and unpivot are very good. However I have some questions which may or may not have to use this. Please suggest with your experience which is suitable for me.

    I have a TABLE in oracle as

    Australia Entry1 Apple $ 2
    Japan Entry2 Guava $2
    Australia Entry2 Guava $2
    Hungary Entry3 Guava $2
    Australia Entry2 Apple $2
    Japan Entry1 Guava $2
    Australia Entry4 Orange $2
    Japan Entry2 Orange $2

    My Output should be as follows where Amount is added w.r.t Entries as well as Fruits but in columns.

    Country Entry1 Entry2 Entry3 Entry4 Apple Guava Orange

    Australias $2 $4 0 $2 $4 $2 $2

    Japan $2 $4 0 0 0 $4 $2

    Hungary 0 0 $2 0 0 $2 0

    I would like the output as above. However I do not want to use decode, which is what I am using right now.

    Is it possible to use this for a varible number of columns. Basically the number of columns will not be more than 10 for sure, but I need them to be variable which is not possible by using decode.

    Thanks for all your help.
    Sreedhar

  16. THis is related to above question. I can move the table to sql if needed. If I know I can get it easier from there.

    THanks,
    Sreedhar

  17. Hi…
    I have a table : T1

    No ID Date Value
    ___________________________________
    1 1001 01/05/2009 101.00
    1 1001 15/05/2009 102.00
    1 1001 20/05/2009 105.00
    2 1001 01/05/2009 41.00
    2 1001 15/05/2009 44.00
    3 1001 01/06/2009 330.00

    I need the Data in this Format

    No ID 01/05/2009 15/05/2009 20/05/2009
    01/06/2009
    ____________________________________________________

    1 1001 101.00 102.00 105.00 NULL
    2 1001 41.00 44.00 NULL NULL
    3 1001 NULL NULL NULL 330.00

    can you give me the solution for this type…

    Thanks in advance

  18. @Mahdestiny

    This is the script.

    — Create Table
    create table T1 ( [No] int, [ID] int, Date Datetime, Value decimal (38,2))

    — Insert data into the table
    insert into T1 values (1, 1001, ’05/01/2009′, 101.00)
    insert into T1 values (1, 1001, ’05/15/2009′, 102.00)
    insert into T1 values (1, 1001, ’05/20/2009′, 105.00)
    insert into T1 values (2, 1001, ’05/01/2009′, 41.00)
    insert into T1 values (2, 1001, ’05/15/2009′, 44.00)
    insert into T1 values (3, 1001, ’06/01/2009′, 330.00)

    — Check the Data
    select * from T1

    — Using Pivot key word to get required output
    SELECT [No] , [ID] ,[05/01/2009] ,[05/15/2009] ,[05/20/2009] ,[06/01/2009]
    FROM (
    SELECT [No], [ID], [Date], [Value]
    FROM T1) up
    PIVOT ( sum([Value]) FOR [Date] in ([05/01/2009] ,[05/15/2009] ,[05/20/2009] ,[06/01/2009]) )AS pvt

    ~ IM.

  19. Here your using

    “SELECT [No] , [ID] ,[05/01/2009] ,[05/15/2009] ,[05/20/2009] ,[06/01/2009]
    FROM ( ”

    i cant say how many dates will be coming from select statement.
    coz im using Select * from T1, this will return me many Date.
    Also im not using sum([Value]) FOR [Date] wise.
    I need to show dynamically date as column and value as row using pivot.

  20. @Mahdestiny,

    Then you need to write Dynamic SQL.

    First prepare the select statement with Dynamic SQL.

    Once you prepare the select statement, then execute that statement.

    ~ IM.

  21. Suppose , Table Sales has 10 product ,I want to Top 5 Sales product
    and sum of the rest of product in single select Query

  22. Hello,

    I have a table ITEM_MST

    ITEM_TYPE QTY Date
    1 2 15 July 2009
    1 4 16 July 2009
    2 3 17 July 2009
    2 1 18 July 2009
    1 4 19 July 2009
    1 6 20 July 2009
    1 2 21 July 2009

    I wan the output in this format

    ITEM_TYPE QTY1 QTY2 QTY3
    1 2 4 4
    1 6 2
    2 3 1

    I dont want to use any aggregate functions as there is nothing to aggregate.

    Please let me know the solution for the same ASAP.

    Thanks and Regards
    Rahul

  23. Hi, Pinal, Robin

    Any further words on using two aggreations in the PIVOT?
    Or is it possible to use an expression in the place of aggreation function? something like (SUM(SellAmount) – SUM(CostAmount))/SUM(SellAmount)*100,
    instead of a single native function.

    thanks a lot

  24. sorry for that post: to keep it short;

    table:

    PARAM VALUE
    k4401 AAA
    k4402 BBB
    k4403 CCC

    Output would look like:

    k4401 k4402 k4403
    AAA BBB CCC

    thanks in advance

  25. @Peter

    create table #Example ([PARAM] varchar (40), [VALUE] varchar (40))

    insert into #Example values (‘k4401′, ‘AAA’)
    insert into #Example values (‘k4402′, ‘BBB’)
    insert into #Example values (‘k4403′, ‘CCC’)

    SELECT [k4401], [k4402], [k4403]
    FROM
    (SELECT [PARAM], [VALUE]
    FROM #Example) p
    PIVOT
    (
    MAX ([VALUE])
    FOR [PARAM] IN
    ( [k4401], [k4402], [k4403])
    ) AS pvt

    ~ IM

  26. @Gary,

    Why don’t you make a computed column in your select list, with what ever expression you have and then name that column , and then in pivot, just use max expression.

    Just give us some sample data, input, and expected output.

    ~ IM.

  27. @Rahul.

    I need more information for your input and output, your output is confusing, please post complete output.

    Here is a sample script.
    SELECT ITEM_TYPE, [1], [2], [3], [4], [5], [6], [7]
    FROM
    (SELECT ITEM_TYPE, QTY , QTY AS QTYY
    FROM Example4) p
    PIVOT
    (
    MAX (QTYY)
    FOR QTY IN
    ( [1], [2], [3], [4], [5], [6], [7] )
    ) AS pvt
    ORDER BY VendorID;

    What I see from your procedure, we can do it through another procedure or script with a while loop, I dont know if that could be done through a simple select statement.

    ~ IM.

  28. VendorName CostCenter DT Amount
    ANCHOR SAFETY INC 91401 5/9/2009 22.58
    ANCHOR SAFETY INC 91401 7/9/2009 63.38
    AMERICAN TOWER 91442 6/9/2009 18.7
    AMERICAN TOWER 91442 8/9/2009 19.19

    If I have the data as above and I like to get the following result:

    ANCHOR SAFETY INC 91401 5/9/2009 28.58 7/9/2009 63.38
    AMERICAN TOWER 91442 6/9/2009 18.7 8/9/2009 19.19

    I tried to use PIVOT in SQL Server 2005 but failed.

    Please help.

    Thank you.

  29. Yeah, pivoting tables are fine and dandy, but when it comes to reporting, (And this is the only case I really needed pivot), most of the reporting tools can not deal with pivots or can not handle unknown number of columns in output data.. etc.
    So I had to make up other solutions…

  30. Hi… I have a Question.. How to write a query for following task..

    INPUT

    Name ENG TAM MAT
    ————————–
    RAJ 52 65 72
    RAM 45 98 75
    JOHN 78 88 52

    I want the output as

    Name RAM
    —————–
    ENG 45
    TAM 98
    MAT 75

    Can anyone help me..???

  31. Hi All,

    I too need the pivoting the below table.

    Current Result by the Query :

    SELECT Genset_Master.EngNo, Genset_Master.AltNo, Genset_Master.CustName, Service_Master.SiteAdd,
    Servicing_History.ServDate
    FROM Service_Master INNER JOIN
    Genset_Master ON Service_Master.Genset_Id = Genset_Master.Genset_Id INNER JOIN
    Servicing_History ON Service_Master.Genset_Id = Servicing_History.Genset_Id

    ==========

    EngNo AltNo CustName SiteAdd ServDate
    ————– — —————————————————-
    001 Alt1 Cust1 Site1 2007-03-06
    001 Alt1 Cust1 Site1 2007-05-11
    002 Alt2 Cust2 Site2 2007-08-05
    002 Alt2 Cust2 Site2 2007-08-12

    I want the result in below format

    EngNo AltNo CustName SiteAdd ServDate1 ServDate 2
    — ———————————————————————-
    001 Alt1 Cust1 Site1 2007-03-06 2007-03-11
    002 Alt2 Cust2 Site2 2007-08-05 2007-08-12

    Please guide me to create query for above result

  32. Hi,Pinal Sir

    is it Possible to use morethan one aggregate function in Pivot table?

    Like

    Select Count(no),sum(value1+value2) ,sum(value3+value4)

    Please Give me solution

    Thnaks in Advance

  33. Thanks for the article. I have a question:
    Using the example in the article, I want to summarize the data for all states, not just the three, without explicitly listing them. something like:

    ….FOR StateProvinceCode
    IN (StateProvinceCode FROM Person.Address)….

    This does not work, I get an error message saying “expecting ID, QUOTED_ID or ‘.’

    Any ideas?

  34. Hi Mr Pinalkumar Dave,
    This article are very good.
    However I have some questions which may or may not have to use this.
    Please support with your experience which is suitable for me.

    I have a table” ProdSchedule
    Model Date upload Lot_qty Prod_Date
    AAA 2010-05-14 1500 2010-05-15
    AAA 2010-05-14 2500 2010-06-18
    AAA 2010-05-21 3500 2010-06-01
    AAA 2010-05-21 4500 2010-05-25
    BBB 2010-05-14 5500 2010-05-05
    BBB 2010-05-21 6500 2010-06-04

    In this case, i need to compare the lot_qty on period month(get from prod_date)
    at every week (this week and last week).

    And I need the output like this format:

    Model Date upload May June
    AAA 2010-05-21 4500 3500
    AAA 2010-05-14 1500 2500
    DiffA 3500 1000
    BBB 2010-05-21 5500 0
    BBB 2010-05-14 0 6500
    DiffB -5500 6500

    Thank You…….

  35. Hi,

    I have a table like this..

    Product element value

    1 FOB 16
    1 Freight 0.25
    1 Burden 16.25
    2 FOB 16
    2 Freight 0.25
    2 Burden 16.25
    2 FOB 18
    2 Freight 1.75

    Now I am writing a query for the pivot table as ..

    SELECT *
    FROM
    (SELECT PRODUCT, ELEMENT, VALUE
    FROM source_table (NOLOCK)
    ) as Data
    PIVOT
    ( AVG(VALUE) FOR ELEMENT IN (WHOLESALE, [FOB], [FREIGHT], [BURDEN])
    )Pivot_Table

    I am getting this as a result.

    PRODUCT FOB FREIGHT BURDEN
    1 16 NULL NULL
    2 17 NULL NULL

    I am getting nulls for the Freight and Burden because (I Think) they have decimal values.

    Can some one help me out in this and tell me how I can get the desired values for these decimal types also?

    Thanks.
    -Praveen.

  36. Hi All,

    The pivot will not work when the columns are varchar fields .

    data

    tick rate
    FIT AAA
    FIT AA+
    SN AAA
    SN AA+
    SN D
    MO Ca
    MO C

    I wanna see
    FIT SN MO
    AAA AAA Ca
    AA+ AA+ C

  37. Hi Jasmeet,
    USE AdventureWorks
    GO
    SELECT [CA], [AZ], [TX]
    FROM
    (
    SELECT sp.StateProvinceCode
    FROM Person.Address a
    INNER JOIN Person.StateProvince sp
    ON a.StateProvinceID = sp.StateProvinceID
    ) p
    PIVOT
    (
    COUNT (StateProvinceCode)
    FOR StateProvinceCode
    IN ([CA], [AZ], [TX])
    ) AS pvt;

    FOR means WHERE. IN this case

    select COUNT(StateProvinceCode)
    FROM P
    WHERE StateProvinceCode
    IN ([CA], [AZ], [TX])

  38. You uses [CA], [AZ], [TX] because you know what is the column,

    what if the column is dynamic? Users can add new items at any time.

  39. hi pinal,

    I have doubt can you please help me..

    Required output in this format.

    empid GS NS MS AS total
    1001 3 2 1 2 8
    1002 1 4 3 0 8

    Below is the table structure
    empid Shift Date
    1001 GS 01-Jan-2012
    1001 GS 02-Jan-2012
    1001 GS 03-Jan-2012
    1001 NS 04-Jan-2012
    1001 NS 05-Jan-2012
    1001 MS 06-Jan-2012
    1001 AS 07-Jan-2012
    1001 AS 07-Jan-2012
    1002 GS 01-Jan-2012
    1002 NS 02-Jan-2012
    …..
    …..
    ….
    1002 AS 07-Jan-2012
    etc…

    Please help me to solve this issue… It’s really urgent
    if you can reply early I’ll really appreciate

  40. Hi

    I have used Pivot in my Stored procedure.

    Like while I was executing, there was a bit server blockings . Is it really due to that. And as per performance point of which is better While Looping is that better or PIVOT is that better.

  41. Hi

    I am looking for a dynamic query which will populate the multiple records from a column into a row with a unique article id. e.g.

    FROM

    Art_Id Product
    101308 BD258
    101309 BD259
    101301 BD214
    101308 BD345
    101309 MD852

    …. more than 2million records

    TO

    ART_ID COL1 COL2 …………. n column
    101308 BD258 BD345
    101301 BD214
    101309 BD259 MD852

    WHEREVER I HAVE SEEN THE HELP BLOG ON IT IT GOT CALCULATION INVOLVED BUT IN MY CASE I DO NOT HAVE ANY CALC. JUST WANT TO TRANSPOSE MY TABLE.

    THANKS IN ADVANCE FOR HELP

  42. sir i want a table like this. my base table is empid,cellphno,officeno,residenceno.but i want data like this manner
    empid,number,type. in number column it display all the three no of empid 1 then it display empid 2.in type if it is cellph it show ‘c’ , if officeno it shows ‘o’ like this.
    please help me…

  43. hourname sublistTitle Day
    1stHour Social Monday
    2nd Hour Maths Monday
    1stHour Telugu TuesDay
    3rd Hour Social WedDay
    1stHour Social FriDay
    3rd Hour Computers FriDay

    query:

    select ch.hourname,dta.sublistTitle,(select d.DayName from DayTable d where d.dayid=t.dayid) as Day from timetable t,classhours ch,(select c.cstuid as cstuid,sl.sublistTitle as sublistTitle from subjectslist sl,subjects_school ss,cst c where sl.sublistid = ss.sublistid and ss.schoolsubuid=c.schoolsubuid and c.suid = 1030) dta where t.houruID = ch.classHouuID and dta.cstuid = t.cstuid and t.classrumid=1004

    I need to get

    Day isthour 2nd hour 3rd hour 4th hour
    Monday Social Maths Null NUll

    Tuseday Telugu Null Null Null

    Wed Null Null Social NUll

    Thursaday NUll Null NUll Null

    Friday Social Null Computers NUll

    Please its Really very important and very urgent so kindly help me as esarly as possible today

    Thaking you

    jyothi

  44. I have these tables.

    table1:

    IDNumber LName Fname Book Section Title
    11355 Smith John CCC 2145 General Idea
    11311 Doe John BBB 2446 General Health
    11433 Doe John DDD 2554 College Politcs
    11377 Brown Ben FFF 2610 How to survive
    11388 Cam Rachel SSS 1274 Hapiness
    11344 Caml Rachel HHH 2845 Solution
    AND

    table 2:

    IDNum Q01 Q02 Q03 Q04 Q05 Q06 Q07
    11355 5 5 5 5 5 5 5
    11355 5 5 5 5 5 5 5
    11355 4 5 4 4 5 4 4
    11322 4 4 4 4 5 4 5
    11322 5 5 5 5 5 5 5
    11300 5 5 5 5 5 5 5
    11300 3 4 3 5 5 4 3
    11300 5 5 5 5 5 5 5
    11300 5 5 5 5 5 5 5
    11300 4 4 4 4 5 4 4
    11311 3 5 4 4 5 5 5
    11311 5 5 5 5 5 5 5
    11377 3 2 3 5 5 5 4
    11377 5 5 5 5 5 5 5

    I need to have a result:

    table 3:

    IDNum QID Lname Room Section Title
    11355 Q1 Smith CCC 2145 General Idea
    11355 Q2 Smith CCC 2145 General Idea
    11355 Q3 Smith CCC 2145 General Idea
    11355 Q4 Smith CCC 2145 General Idea
    11355 Q5 Smith CCC 2145 General Idea
    11355 Q6 Smith CCC 2145 General Idea
    11355 Q7 Smith CCC 2145 General Idea
    11322 Q1 Doe BBB 2246 General Health

    Thank you,

  45. Suppose…I have the following rows in a table MEMBERSHIP_NO, CLASS, LASTNAME, FIRSTNAME, MiddleName, HOME_ST, HOME_CITY, HOME_PROV_STATE, HOME_CTRY, HOME_PC, HOME_PHONE, POSITION with their values. I want to use Pivot on these columns but these rows might not be present for each of the MemberIDs. Some columns are missing for some members depending on the data availability. How can I handle the missing data rows (columns) in my Pivot Report?

  46. Hi ,

    Its really great post. I think i can solve my current problem with PIVOT.

    Can anyone please help how to do this with PIVOT. I also tried but didnt get the exact out put what i want.

    Available Data

    EmpCode PayCode Value
    ————————————————
    E001 HRA 2000
    E001 BASIC 45000
    E001 TA 5000

    The output format how I want

    EMPCODE HRA BASIC TA
    ——————————————————————
    E001 2000 45000 5000

    Thanks in advance.

    Plahanov

  47. Hi Pinal Sir,
    I have doubt can you please help me..I am very new in s/w DEvelopment. below i am describing.

    Original Table Structure.

    ID Date TimeLogged UserName
    1 10/8/2012 5.5 Bubai
    2 11/8/2012 2.3 Bubai
    3 10/8/2012 3.3 Bhanu
    4 11/8/2012 7.3 Bhanu

    I want result like below. User Name should be dynamic.May be lot of users.User name will come from Database table. Please give me broad description as I am very new in the system,for my understanding.

    Date Bubai Bhanu Total
    10/8/2012 5.3 3 8.3
    11/8/2012 2.3 7.3 10
    Total 8 10.3 18.3

  48. I having table Employee and Production tables.

    To show reports for target and achieved Value for the every month for an employee.

    below resulted table example:

    Name Date Target Value
    Admin 8/17/2012 0 8
    Admin 8/18/2012 0 6
    Admin 8/22/2012 0 1
    Admin 8/23/2012 0 11
    Admin 8/24/2012 0 3
    Admin 8/26/2012 0 2
    Babu 8/17/2012 500 376
    Babu 8/18/2012 500 313
    Babu 8/21/2012 500 172
    Babu 8/22/2012 500 305

    Pivot

    Name 1T 12T … Nt 1V 2V Nv
    Admin 0 0 0 0 8 6 0
    Babu 500 500 500 0 376 313 0
    .
    .

    with regards

    Merlin.P

  49. Hi i want to ask something about my work

    select * from
    (Select m.Brand,u.Color,Count(Unit)as [Total unit] from Brands m inner join Products u on m.BrandID=u.ProductID
    Group by m.Brand ,u.Color with cube)as Pvt
    PIVOT
    (
    Sum([total unit])
    For Color
    IN
    (
    [Black],[White],[Gray],[Silver],[Orange],[Dark Blue]
    )
    )AS Pivot_Table

    I want to show total Brand and Total color but this code just calculate each color and count every color per brand

    How can i get Total Brand and Total Color ????

    Please help me

  50. I have Table 2
    Table A
    ShowId,ShowName,
    1,Show1
    2,Show2
    :
    :
    :
    End Not know

    DanceId,DanceNumber,DanceName
    1,1,1
    2,1,2
    3,2,1
    :
    :
    :
    End Not Know

    I am looking for result like

    Row 1:Show1,Show2,Show3………………….
    Row 2: DaneNumber,DanceNume……
    Row 3: DaneNumber,DanceNume……
    :
    :
    How can we get this

  51. hi sir plz help for this problem

    In my table the values are stored as
    empid ename date status
    ————————————————
    101 abc 01/02/2013 present
    101 abc 02/02/2013 present
    101 abc 03/02/2013 absent
    102 xyz 03/02/2013 present
    and so onnnn
    101 abc 28/02/2013 present
    102 xyz 28/02/2013 present

    here i mention only one employe record.There is n number employes there my problem is
    how to show table like this

    empid ename 1 2 3 4 5 6 …….28
    ————————————————————————
    101 abc present present absent…………. presnt
    102 xyz present present present………..present

    here 1 2 3 4 represent dates of the month.

    plese kindly provide me the query to get like this.
    in this above case empid is also varchar

    thanks.

  52. Pingback: SQL SERVER – Weekly Series – Memory Lane – #030 | SQL Server Journey with SQL Authority

  53. Hi! I have table with column as show below
    x Date Noofitems
    1 12/06/2013 23
    2 12/06/2013 14
    3 15/06/2013 50

    I need to get the record as follows
    x 12 15
    ————————
    1 23 –
    2 14 –
    3 – 50

    Pls help me.

  54. Hello,

    I having urgent requirement on Given Data to convert it into another format.

    id Qdate Qty POID
    1 2013-07-11 1000 33
    2 2013-07-12 500 33
    3 2013-07-12 500 33

    Converting it into Below Format

    Quantity Recd. #1 Date1 Quantity Recd. #2 Date2 Quantity Recd. #3 Date3
    1000 2013-07-11 500 2013-07-12 500 2013-07-12

  55. Hi
    I am new to Pivot can someone please help with below issue.

    Thanks in advance

    Table Structure
    State_ID Customer_ID Field_Desc Field_Value start_date end_date
    1234 50045 Field1 Result 1 00:00.0 00:00.0
    1234 50045 Field2 res 2 00:00.0 00:00.0
    1234 50045 Field3 res 3 00:00.0 00:00.0
    1234 50045 Field4 res 4 00:00.0 00:00.0

    Desired Result
    State_ID Customer_ID Field1 Field2 Field3 Field4
    1234 50045 Result 1 res 2 res 3 res 4

    My Result
    State_ID Customer_ID Field1 Field2 Field3 Field4
    1234 50045 Result 1 NULL NULL NULL
    1234 50045 NULL res 2 NULL NULL
    1234 50045 NULL NULL res 3 NULL
    1234 50045 NULL NULL NULL res 4

    My Script
    Select State_id,Customer_id,Field1,Field2,Field3,Field4
    from #data
    PIVOT
    ( max(field_value)
    for Field_Desc in (FIeld1,Field2,Field3,Field4)
    ) pvt
    where customer_id is not null
    and state_id=1234

  56. How to use cast with aggegatate funtion while doing pivot.
    i m trying to do cast to decimal while doing avg in pivot,its throwing error:’cast’ is not a recognized aggregate function.

  57. I’ve a table for sample name that contains the following columns firmnesssID, SampleTestingID and Firmness:
    firmnesssID SampleTestingID Firmness
    1 243 1.8
    2 243 1.9
    … …. …..
    21 244 7.9
    Now, I need to display output like the below. I’m assuming there are maximum 40 records for a particular SampleTestingID and I want to show Firmness values under column header like 1a 1b 2a 2b 3a 3b … 20a 20b (40 columns) for every distinct SampleTestingID.

    SampleTestingId 1a 1b 2a 2b 3a 3b 4a 4b 5a 5b 6a 6b 7a 7b 8a 8b 9a 9b 10a 10b 11a 11b 12a 12b 13a 13b 14a 14b
    243 6.70 6.66 6.13 7.33 6.25 7.19 6.81 6.54 7.78 7.59 7.86 9.71 8.40 7.10 7.82 6.46 4.94 6.24
    246 7.79 7.14 6.13 6.31 7.68 5.39 9.27 8.53 6.84 6.89 7.05 5.84 4.86 5.97 6.48 5.44 6.70 7.21 7.68 6.86

    • I also tried to achieve the same by PIVOT the data but I was unable to get desired output. Here is what I was doing:

      SELECT P.SampleTestingId, IsNull(P.[1], 0) as 1a, IsNull(P.[2], 0) as 1b
      , IsNull(P.[3], 0) as 2a, IsNull(P.[4], 0) as 2b
      , IsNull(P.[5], 0) as 3a, IsNull(P.[6], 0) as 3b
      , IsNull(P.[7], 0) as 4a, IsNull(P.[8], 0) as 4b
      , IsNull(P.[9], 0) as 5a
      FROM
      (
      SELECT SampleTestingId, Firmness,[FirmnessID]
      FROM dbo.ftatestdata WHERE SampletestingId=243 AND Isdeleted=0
      ) I
      PIVOT
      (
      SUM([FirmnessID])
      FOR [Firmness] IN ([1], [2], [3], [4], [5], [6], [7], [8], [9])
      ) as P

      It provides header structure but data displayed 0 always.

      and

      DECLARE @cols NVARCHAR (MAX)
      SELECT @cols = COALESCE (@cols + ‘, [' + Convert(varchar, FirmnessID) + ']‘, ‘[' + Convert(varchar, FirmnessID) + ']‘)
      FROM FtaTestData
      WHERE SampleTestingID=243 AND IsDeleted=0 order by SampleTestingID
      print @cols

      DECLARE @Query VARCHAR(MAX)

      SET @Query= ‘SELECT ‘+ @cols +’ FROM (SELECT FirmnessID, Firmness
      FROM FtaTestData WHERE SampleTestingID=243 AND IsDeleted=0) P PIVOT ( MAX(Firmness) FOR FirmnessID IN ( ‘+@cols+’ ) ) AS pvt’

      print @Query

      EXEC(@Query)

  58. hello sir , i have a problem how to automatically update sql table. but my requirement is i given date-time suppose (01/01/2022) that particular date update my sql table how?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s