Transforming rows to columns (PIVOT/CROSS TAB) and columns to rows (UNPIVOT) may be one of the common requirements that all of us must have seen several times in our programming life. SQL Server 2005 introduced two new operators: PIVOT and UNPIVOT that made writing cross-tab queries easier.
My friend and SQL Server MVP Jacob Sebastian has posted an example that transform rows to columns using PIVOT operator. The reverse operation of PIVOT is UNPIVOT. PIVOT operator is available only in SQL Server 2005/2008. It does not exists in SQL Server 2000. Developers who are still using SQL Server 2000 should upgrade to SQL Server 2005 or SQL Server 2008 (recommended).
While discussion this issue with Jacob another day he wrote following posts so fast it was very impressive.
TSQL Lab 15 – Another PIVOT Query Example
TSQL Lab 16 – Generating CROSS TAB results in SQL Server 2000
TSQL Lab 17 – An example using UNPIVOT (Transforming Columns to Rows)
Articles written by me previously.
SQL SERVER – PIVOT and UNPIVOT Table Examples
SQL SERVER – PIVOT Table Example
SQL SERVER – UNPIVOT Table Example
Reference : Pinal Dave (https://blog.sqlauthority.com)
15 Comments. Leave new
my problem is:
1 table store IDNo, ItemName,itemvalue,itemamount.
other table store IDNo,tax1,tax2,party1,party2,party3,
I went to show like this
party1 party2 party3
itemname 200 210 220
tax1 10 11 12
tax2 1 3 4
but I try not do liks this .
if any solution please tell me.
dinesh
You need to post some data as well as expected result to help you
I have this type of Data:
Here Investigation Date is Combo-Box, where date can be selected:
——————————————————————————————
Investigation Date 21/09/2011
——————————————————————————————
Test Name Result Unit Range Comments
——————————————————————————————
Blood Test 1.5mg mg 1.0-2.5 —–
Blood Test 2.0mg mg 1.0-2.5 —–
Urine Test 7.5mg/dl mg/dl 4.0-15.5 —–
——————————————————————————————
See Above 2 Blood test are inserted, While first test is done on 21/09/2011 & second on 25/09/2011.
And Urine Test Done on 25/09/2011.
While i had use this access query in VB.Net coding Directly which runs successfully.
========================================================
Access Query:
TRANSFORM First(dtInvestigation.InvValue & ‘ ‘& InvUnit) AS FirstOfInvId SELECT mstInvestigation.InvstName FROM dtInvestigation INNER JOIN mstInvestigation ON dtInvestigation.InvId = mstInvestigation.InvstId Where dtInvestigation.InvDate Is Not Null and Patid=1 GROUP BY mstInvestigation.InvstName PIVOT (dtInvestigation.InvDate) as pvt;
========================================================
*** Now i want the data like this ***
——————————————————————————————
Test Name 21/09/2011 25/09/2011
——————————————————————————————
Blood Test 1.5mg 2.0mg
Urine Test 7.5mg/dl
——————————————————————————————
Please make it fast and send me the reply as soon as possible
You can reply me here :- [email removed]
Regards,
Amit Uttekar
[email removed]
i have one table called datamart…in which 1 column named quantity exists… quantity has a data such as 2.00,3.00 & etc…
when i select a data through query…
i.e. select quantity from datamart
it shows me a data like…
quantity
2.00
3.00
and so on..
but i want to have a data in following format…
quantity 2.00 3.00 and so on..
please help me on this soon…
Amisha
SQL Problem
Assume we have loaded a flat file with patient diagnosis data into a table called “Data”. The table structure is:
Create table Data (
Firstname varchar(50),
Lastname varchar(50),
Date_of_birth datetime,
Medical_record_number varchar(20),
Diagnosis_date datetime,
Diagnosis_code varchar(20))
The data in the flat file looks like this:
‘jane’,’jones’,’2/2/2001′,’MRN-11111′,’3/3/2009′,’diabetes’
‘jane’,’jones’,’2/2/2001′,’MRN-11111′,’1/3/2009′,’asthma’
‘jane’,’jones’,’5/5/1975′,’MRN-88888′,’2/17/2009′,’flu’
‘tom’,’smith’,’4/12/2002′,’MRN-22222′,’3/3/2009′,’diabetes’
‘tom’,’smith’,’4/12/2002′,’MRN-33333′,’1/3/2009′,’asthma’
‘tom’,’smith’,’4/12/2002′,’MRN-33333′,’2/7/2009′,’asthma’
‘jack’,’thomas’,’8/10/1991′,’MRN-44444′,’3/7/2009′,’asthma’
You can assume that no two patients have the same firstname, lastname, and date of birth combination.
The problem is this: Tom Smith has 2 different medical record numbers. Write a query that would always show all the patients who are like Tom Smith – patients with more than one medical record number.
This problem has many solutions, but if you know SQL, you should be able to find one that uses a single query with no subqueries.
I am kindly Requeistng to you please te me how i can insert different fields in different tables from one form
Iwant to display report as same column name (repeat)and diffrent fields are display in diffrent column by using pivot. what can Ido?
where is the solution of above querys..
select * from (SELECT DISTINCT(T.VCH_YEAR),CONVERT(VARCHAR,T.NUM_QUANTITY)+T.VCH_UNIT AS QUANTITY ,CONVERT(VARCHAR,M.DTM_PLAN_APPROVAL,106) AS DTM_PLAN_APPROVAL FROM M_MIS_MININGPLAN_ML M,T_MIS_MININGPLAN_ML T
WHERE M.INT_MP_ID=T.INT_MP_ID AND M.BIT_DELETED_FLAG=0 AND T.BIT_DELETED_FLAG=0 and m.INT_CIRCLE_ID=7 AND T.INT_MINERAL_ID=13 AND M.INT_LESSEE_ID=271
GROUP BY VCH_YEAR,CONVERT(VARCHAR,T.NUM_QUANTITY)+T.VCH_UNIT,DTM_PLAN_APPROVAL )
gettable PIVOT (Max(DTM_PLAN_APPROVAL) FOR QUANTITY IN
(Can i write theselect statement here.))AS riii
Hi and thanks for all the great information! I have a scenario I need help with and I am hoping you can help me out.
I have a table that looks like this;
EMP NAME | STAT NAME | STAT | WEEK
“JACK” | “NUMBER OF VISITS” | 12 | 1
“JACK” | “NUMBER OF VISITS” | 22 | 2
“SUSAN” | “NUMBER OF CALLS HANDLED” | 125 | 1
“SUSAN” | “NUMBER OF CALLS HANDLED” | 105 | 2
My problem is that I need to return a single row for each “Stat Name” per employee so that my data set would look like this;
EMP NAME | STAT NAME | WEEK 1 | WEEK 2
“JACK” | “NUMBER OF VISITS” | 12 | 22
“SUSAN” | “NUMBER OF CALLS HANDLED” | 125 | 105
I am kinda lost on what the right thing to do is to get this type of result. Any suggestions?
Thanks in advanced!
I Have One Query
I want to Dispaly Out Put as like
agentId | Mon | tue | wed | thur | fri | sat | sun
1 5 pm 5 pm 5 pm 5 pm 5 pm 5 pm 5 pm
======================================
My Table like this
CREATE TABLE [dbo].[CRMScheduleMaster](
[ScheduleID] [int] IDENTITY(1,1) NOT NULL,
[AgentID] [int] NOT NULL,
[WeekID] [int] NULL,
[StartTime] [varchar](50) NULL,
[EndTime] [varchar](50) NULL,
CONSTRAINT [PK_ScheduleMaster] PRIMARY KEY CLUSTERED
(
[ScheduleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
================
So pls Give the Solution.
Mu\y Id is : [email removed]
i reallly want your help in makin a cross tab query … here is the
scnerio . plzzz help me ….
there are many tables .
tb
datatype
sno autogenerate , date varchar(16) , time varchar(10) ,rhrs int,
sno date time rhr_s
1 26/01/2012 07.00 2801
1 26/01/2012 08.00 2802
1 27/01/2012 09.00 2803
1 27/01/2012 10.00 2804
tb1
datatype
sno autogenerate , date varchar(16) , time varchar(10) ,rhr_s1 int,
sno date time rhr_s
1 26/01/2012 07.00 2801
2 26/01/2012 08.00 2802
3 26/01/2012 09.00 2803
4 26/01/2012 10.00 2804
5 27/01/2012 07.00 2811
6 27/01/2012 08.00 2812
7 28/01/2012 09.00 2813
8 28/01/2012 10.00 2814
tb2
datatype
sno autogenerate , date varchar(16) , time varchar(10) ,rhr_s2 int,
sno date time rhr_s2
1 26/01/2012 07.00 2811
2 26/01/2012 08.00 2812
3 27/01/2012 09.00 2813
4 27/01/2012 10.00 2814
i want a crross tab in this way in date format randomly no fixed date
**cols ————–26/01/2012—————————-27/01/2012
—————— 28/01/2012**
rhr_s–(max-min where date = 26/01/2012) (max-min where date =
27/01/2012) (same formula)
rhr_s1 as above as above
as above
rhr_s2 as above as above
as above
please help me in making this type of report …..
i reallly i trouble
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.
thanks.
in the above case the empid is also varchar
If you want multiple PIVOT columns, you can use this procedure
https://madhivanan.wordpress.com/2017/07/06/dynamic-crosstab-with-multiple-pivot-columns/