Transact SQL :: Group By And Pivot Several Columns

Oct 2, 2015

I have a table which I would like to group on several columns, and for the Contract number, I'd like a maximum of four different columns which would contain pivoted information.

Here is my DDL:

CREATE TABLE [dbo].[SV00403](
[CUSTNMBR] [char](15) NOT NULL,
[ADRSCODE] [char](15) NOT NULL,
[Contract_Number] [char](11) NOT NULL,
[WSCONTSQ] [int] NOT NULL,

[Code] ....

Here is my select statement:

SELECT[CUSTNMBR]
,[ADRSCODE]
,[Contract_Number]
,[WSCONTSQ]
,[Equipment_ID]

[Code] ...

Here are the results of the select statement:

And here is what the result set is that I would like to achieve:

The yellow indicates the group by columns. How do I pivot the contract number into the four columns noted above ?

View 8 Replies


ADVERTISEMENT

Transact SQL :: Pivot With Multiple Columns

Sep 1, 2015

I have one table like this.

-- drop table #temp
create table #temp(ID bigint, Description varchar(50), ET varchar(200), ET_Status varchar(50), ET_Date datetime, ET_IsValid varchar(3))

insert into #temp
select * from (values (1,'Test','A', 'Ack','08/15/2015', 'Yes'),(1,'Test','B', 'Nack','08/17/2015', 'Yes'),(1,'Test','C', 'Ack','08/21/2015', 'Yes')) a(ID, Description, ET, ET_Status, ET_Date, ET_IsValid)

I want to pivot this. My expected result look like this.

ID - Description - ET_A_Status - ET_A_Date
- ET_A_IsValid -  ET_B_Status - ET_B_Date
  - ET_B_IsValid - ET_C_Status  - ET_C_Date
-
ET_C_IsValid 

1  - Test    - 'Ack'       - '2015-08-15 00:00:00.000'  - 'Yes'   -  'Nack'  - '2015-08-17 00:00:00.000'  - 'Yes'  - 'Ack'   - '2015-08-21 00:00:00.000' -  'Yes'

View 6 Replies View Related

PIVOT/CROSS TAB/Converting Rows To (multiple Group) Columns

Aug 3, 2007

Hello All,

I am trying to convert the rows in a table to columns. I have found similar threads on the forum addressing this issue on a high level suggesting the use of cursors, PIVOT Transform, and other means. However, I would appreciate if someone can provide a concrete example in T-Sql for the following subset of my problem.

Consider that we have Product Category, Product and its monthly sales information retrieved as follows:













CategoryID
ProductID
ProductName
Month
UnitPrice
QtySold
SalesAmount

1
1
Panel
Jan
5
10
50

1
1
Panel
Feb
5
15
75

1
1
Panel
Mar
5
20
100

1
2
Frame
Jan
10
30
300

1
2
Frame
Feb
10
25
250

1
2
Frame
Mar
10
20
200

1
3
Glass
Jan
20
10
200

1
3
Glass
Feb
20
20
400

1
3
Glass
Mar
20
30
600

I would like it to be converted into following result set:















CategoryID
ProductID
ProductName
UnitPrice
QtySold_Jan
SalesAmt_Jan
QtySold_Feb
SalesAmt_Feb
QtySold_Mar
SalesAmt_Mar

1
1
Panel
5
10
50
15
75
20
100

1
2
Frame
10
30
300
25
250
20
200

1
3
Glass
20
10
200
20
400
30
600

I have purposefully included QtySold here as I need to display both Quantity and Sales as measured column groups in my report. Can this be achieved in sql? I would appreciate any responses.

Thanks.

View 1 Replies View Related

Transact SQL :: SSMS Pivot With Dynamic Columns

Apr 23, 2015

I found this Microsoft article for creating crosstab-like queries in SSMS.Is it possible, however, to create this same query if I do not know what the values for the columns will be?  Using their example for my problem, I will not know what the values in the "IN" criteria will be because my query would be for a "rolling" 12 months (thus causing that IN criteria to change every month).I've tried declaring variables to pull in the values, but since this will eventually go into a view, I don't think that I can use declared variables.

View 3 Replies View Related

Transact SQL :: Transpose (Pivot) Columns To Rows?

Aug 15, 2015

I need to pivot my records in columns into rows. Here is sample data:

create table #columnstorows
(
Payer varchar (5),                  
ID varchar (5),      
ClM varchar (2),                 
Paid1 float,
Paid2 float,
Paid3 float
)                      

[code]....

Desired result:

Payer1 Payer2
ID1 ID2
Paid1A Paid1B
Paid2A Paid2B
Paid3A Paid3B
U001 U002
 001 002
76.58 19.53
 153.48 96.43
 53.48   200

View 10 Replies View Related

Transact SQL :: Pivot Columns Into Rows With Difference

Sep 22, 2015

Here is the table:

empid lastname firstname title titleofcourtesy ModifiedOROriginal

1 Davis1 Sara CEO Ms. Modified
1 Davis Sara CEO Ms. Original

We need an output like this:

empid
1
1

lastname
Davis1
Davis

firstname
Sara
Sara

title
CEO
CEO

titleofcourtesy
Ms.
Ms.

ModifiedOROriginal
Modified
Original

View 2 Replies View Related

Transact SQL :: Group By With Non-aggregate Columns

Aug 5, 2015

How can I aggregate this result into 1 row? (I got it from a UNION ALL)

Article         Assort1    Assort2
50095811    K1             NULL
50095811    NULL          K3

I would like to have

Article         Assort1    Assort2
50095811    K1             K3

View 3 Replies View Related

Transact SQL :: How To Select Columns That Are Not In GROUP BY And Get COUNT

Jul 3, 2015

I am using SQL 2012.  I have a GROUP BY and I want to select two other fields from my table at the same time: One column that is a string (account_code) and one that I need to perform a count on (customer_number).  I know the code COUNT(DISTINCT customer_number) works for getting that.   I need to select both of those fields on top of what I have.  I have the following:

DECLARE @Providers TABLE (ID INT IDENTITY(1,1),
Provider_Name VARCHAR(20),
Uniq_Id VARCHAR(10),
Total_Spent MONEY,
Total_Earned MONEY)
INSERT INTO @Providers (Provider_Name, Uniq_Id,Total_Spent,
Total_Earned)

[Code] .....

View 21 Replies View Related

Transact SQL :: Select Multiple Columns From Table But Group By One Column

Jun 17, 2015

I have a SQL query like this

select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode, CurrencyCode,TransactionAmount order by CurrencyCode

As per this query I got the result like this

CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED     BNT    1     1
AED     BNT     12     1
AED     SCN     1     1
AED     SNT     1     3

[Code] ....

But I wish to grt result as

CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED     BNT   13     2
AED     SCN     1     1
AED     SNT     11     7
AFN     BPC    8     6

[Code] ....

I also tried this

select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans]
from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date)
group by TransactionCode order by CurrencyCode

But of course this codes gives an error, but how can I get my desired result??

View 5 Replies View Related

T-SQL (SS2K8) :: Select Group On Multiple Columns When At Least One Of Non Grouped Columns Not Match

Aug 27, 2014

I'd like to first figure out the count of how many rows are not the Current Edition have the following:

Second I'd like to be able to select the primary key of all the rows involved

Third I'd like to select all the primary keys of just the rows not in the current edition

Not really sure how to describe this without making a dataset

CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,

[Code] .....

Group by fails me because I only want the groups where the Edition_fk don't match...

View 4 Replies View Related

Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?

Apr 29, 2015

I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:

I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.

1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B

If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.

2. My second question: How to i get around this error?

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B,  fnSplitJson2(A.ITEM6,NULL) C

I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.

View 14 Replies View Related

SQL Server 2012 :: How To Pivot Column To Rows Within A Group

Dec 18, 2014

I need to convert the column to rows but within group. example

Group Name Value
p a 1
p b 2
p c 3
p d 4
q a 5
q b 6
q d 7
r a 8
r b 9
r c 10
r d 11

This need to be transposed to :

Group a b c d
p1234
q56NULL7
r891011

View 3 Replies View Related

Power Pivot :: Calculation Based Upon Subtotal In Group

Aug 4, 2015

PowerPivot 2013. In this example I am trying to get revenue per employee (highlighted in yellow)

In order to do that I need the numerator to appear in every row (red arrow in subtest)

I thought this would do the trick, but ...

=CALCULATE(sum('JOB COST DETAILS'[Job Line Income Amount]),ALLEXCEPT(Employee,Employee[Employee Name]))

View 7 Replies View Related

Transact SQL :: Using A Like Statement In Pivot

Oct 27, 2015

I have a pivot SQL like below SQL Server 2012

SELECT Production_Date, Production_Order, LogicalVat KPI_Category,
'Probiotic Amt Consumed' KPI_Data1_Name,'RC Amt Consumed' KPI_Data2_Name
FROM
( SELECT Production_Date , NULL Production_Order,
LogicalVat, ReportValue ReportValue
FROM BIReports.dbo.r_VatMake
WHERE Production_Date between '10/27/2015' and '10/27/2015'

[code].....

Now the attributes changed into like below where the number after RC is dynamic and I can't use the query above anymore

Example:
RC Amt Consumed - RC 6
RC Amt Consumed - RC 7
RC Amt Consumed - RC 8

Probiotic attribute changed into like below where number after PROB is dynamic

Probiotic Amt Consumed - PROB 15
Probiotic Amt Consumed - PROB 16
Probiotic Amt Consumed - PROB 17

View 5 Replies View Related

Pivot On Dynamic Columns

May 11, 2006

I have a table with 40k terms and I need to map these to a set of objects where each object is represented as a column(tinyint). The object/column name is represented as a guid and columns are added/removed dynamically to support new objects for a set of terms.

I can get the rows needed:

guid1guid2guid3guid4guid5
================================
01100
01101

I think I need to then convert this set of rows to a table which I can join to the object runtime table to start these objects if the column has a count/sum greater than 0. This is the table I think I need in order to join on guids to the runtime table:

NAME Count
===========
guid10
guid22
guid32
guid40
guid51

I don't know how to construct this table for the former table. I think it may be a pivot table, but I don't know. I have the column names:

SELECT NAME
FROM SYSCOLUMNS
WHERE ID = OBJECT_ID(#Temp)
ORDER BY COLID

NAME is a sysname, which doesn't seem to cast into a guid, also a problem when joining the runtime table with this #Temp table.

I also don't want to use a cursor to construct a table.


Thanks for any help,
James

View 1 Replies View Related

Re-arrange Columns After Pivot

Oct 1, 2013

I am workong on this Pivot select. At it is last part, I want to find a way to re-arrange the column order. Here is the test data:

if OBJECT_ID ('tempdb..#temp') is not null drop table #temp
go
create table #temp(b_date varchar(15), Gender varchar(5), Admin_date varchar(15), S_ID int, TEST_NAME varchar(5),SCHOOL_YEARvarchar(5), PART_DESCRIPTIONvarchar(15),Test_Score int)

[code]....

But the output requries the last couple columns to be SCHOOL_YEAR, TEST_NAME, i.e. they come after the scores.I thought the easiest way will be to select into #temp2, then extract from there. Even that, I couldn't get that to work with the select pivot.

View 4 Replies View Related

Table With Two Columns To Pivot

Aug 20, 2014

I have a table with this data structure (Before section)

I have the query below to generate date as in (After section)

But I would like to have (Desired section) view where start and end tasks are next to each other

Here is the query
-----------------------------

SELECT
ID
,[Final] AS [Final Start]
,[Edit] as [Edit Start]
,[Proof] as [Proof Start]
,[Stage] as [Stage Start]
,[Marketing] as [Marketing Start]

[Code] ....

View 2 Replies View Related

Pivot Of Data To Columns

Apr 22, 2015

I have a simple query showing customer net invoice value by month and work type. What I would like to do is to summarise the data so that for every customer I see the net invoice value for each work type for each month.

I would like to see this as: Group, Customer, Period and then the individual work types, of which there are always 8, as columns with the net invoice value under each of them. How can I accomplish this best? Sample code and results are below:

select [Group], [Customer], [Period], [Work_Type], Isnull([Invoiced],0) [Net] from vw_R_Damages

Group Customer Period Work_Type Net
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------- -------------------- ---------------------
JARVIS CONTRACTING LIMITED JARVIS CONTRACTING LIMITED 2011-12 Breakdown / Call out 0.00
JARVIS CONTRACTING LIMITED JARVIS CONTRACTING LIMITED 2011-12 Breakdown / Call out 0.00
BARRATT BARRATT NORTHAMPTON 2011-12 Breakdown / Call out 0.00

[code]...

View 1 Replies View Related

Pivot Multiple Columns

Sep 19, 2007

I have a table the records the results of three different tests that are graded on a scale of 1-7. The table looks something like this.
PersonId TestA TestB TestC

1 4 5 4
2 6 2 4
3 5 5 6
4 1 5 1

I would like to have a SQL statement that would pivot all this data into something like this

Test 1 2 3 4 5 6 7
A 1 0 0 1 1 1 0
B 0 1 0 0 3 0 0
C 1 0 0 2 0 1 0

Where the value for each number is a count of the number of people with that result.

The best solution that I have been able to come up with is to pivot each test and UNION ALL the results together. Is there a way to do this in a single statement?

(If this has already been covered I apologize, but I could not find the solution.)


View 3 Replies View Related

Using Two SUM Columns In A Pivot Table

Feb 6, 2008



I currently have a pivot table that is working great but I need to add to it. The below code is giving me the total ServiceTime per date, which is dynamic. I need to split this service time out depending on the stage of this note.

1) If the note has been signed
2) If the note has been signed and countersigned

SUM(CASE WHEN countersigned_id IS NULL AND signed_id IS NOT NULL THEN ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) ELSE
0 END) as PendingServiceTime,

SUM(CASE WHEN countersigned_id IS NOT NULL THEN ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) ELSE
0 END) as ApprovedServiceTime

How do I add this to my pivot table query? I am thinking that I need to have two seperate queries and join them together some how.


SELECT lastname + ', ' + firstname as FullName, [12/3/2007], [12/4/2007], [12/5/2007]
FROM (SELECT p.LastName, p.FirstName, t.ServiceDate,

ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) AS ServiceTime

FROM dbo.allNotes(8) AS t
LEFT JOIN dbo.note_Collateral_provider AS d ON d.note_Collateral_id = t.ID
LEFT JOIN dbo.Personnel as p ON d.personnel_id = p.ID
LEFT JOIN dbo.Clients as c on t.ClientID = c.ID
LEFT JOIN fPayor(8) fp on fp.noteId = t.id and fp.dbTable = 'collateral'
LEFT JOIN dbo.payor py ON py.ID = substring(fp.fPayorName, 41, 19)
LEFT JOIN dbo.payorinfo pyInfo ON pyInfo.ID = py.payorinfoid
WHERE t.AgencyID = 8 AND t.tableName = 'collateral'
AND t.not_billable_reason_id IS NULL AND VOID_ID IS NULL
AND ((t.signed_id IS NOT NULL AND t.countersigned_id IS NULL) OR (t.countersigned_id IS NOT NULL))
AND t.ServiceDate BETWEEN CONVERT(DATETIME, '12/03/2007') AND CONVERT(DATETIME, '12/05/2007')
) rs Pivot (SUM(rs.ServiceTime) FOR rs.ServiceDate IN ([12/3/2007], [12/4/2007], [12/5/2007]

View 3 Replies View Related

Multiple Columns As The Pivot Key

Aug 27, 2007

Hello,

Here is a sample of the data that I am trying to pivot;

rec_id sequence field_name value
1 1 cat_nbr Granrier
1 1 cat_page pg 21
1 2 cat_nbr H&S
1 2 cat_page pg234
2 1 cat_nbr Ford
2 1 cat_page pg5

I need to pivot on rec_id and sequence to get an output like this:

rec_id sequence cat_nbr cat_page
1 1 Granrier pg21
1 2 H&S pg234
2 1 Ford pg5

All I seem to be able to get thoug is this:
rec_id sequence cat_nbr cat_page
1 1 Granrier
1 1 pg21
1 2 H&S
1 2 pg234
2 1 Ford pg5



It seems to me that the pivot transform can only pivot around one key value column. What am I missing?

Thanks.

View 4 Replies View Related

Transact SQL :: Pivot And Invalid Column Name

May 13, 2015

I'm trying to Pivot and I keep getting an "Invalid Column Name" error, which I can't figure out since, if I run the query and exclude the Pivot statement, the query runs fine.

Columns
ItemNmbr Char(31) not null
SetupTime_I Numeric(19,5) not null
WCID_I  Char(11) not null
select ItemNmbr,SetupTime_I, WCID_I from RT010130
Results

Now run
select ItemNmbr,SetupTime_I, WCID_I
from RT010130
pivot (sum(SetupTime_I) for WCID_I in ([BLA01],[URE02])) PVT

And I get an Invalid Column Name error for both SetupTime_I and WCID_I - which, as far as I can tell, is demonstrably incorrect.

View 5 Replies View Related

Transact SQL :: Pivot On Multiple Results

Nov 9, 2015

I have a table similar to below:

itemID | part
1         | A
1         | B
2         | A
2         | A
2         | A
3         | C

I need the table to look like the following:

itemID | part1 | part2 | part 3
1         | A        | B       | null
2         | A        | A       | A
3         | C        | null    | null

There will _never_ be more than three parts to an item, and it does not matter what order they are in.

I cannot get pivot to work for me.

View 2 Replies View Related

Help With A Pivot Table (dynamic Columns)

Feb 15, 2008

I have the following Pivot Table:



Code Snippet
Declare @tblEquipment Table
(
numEquipmentID INT,
txtManufacturer nvarchar(30),
txtModel nvarchar(30)
)
Declare @tblEquipmentFields Table
(
numFieldNameID INT,
txtFieldName nvarchar(25)
)
Declare @tblEquipmentDetails Table
(
numEquipmentDetailsID INT,
numEquipmentID INT,
numFieldNameID INT,
txtFieldValue nvarchar(30)
)
Insert INTO @tblEquipment Values(23, 'Dell', 'Optiplex 270')
Insert INTO @tblEquipment Values(26, 'Dell', '1705FP')
Insert INTO @tblEquipment Values(42, 'Dell', 'Optiples 745')
Insert INTO @tblEquipmentFields Values(1, 'Monitor Size')
Insert INTO @tblEquipmentFields Values(2, 'Processor Type')
Insert INTO @tblEquipmentDetails Values(1077, 23, 2, 'P4M')
Insert INTO @tblEquipmentDetails Values(1146, 26, 1, '17')
Insert INTO @tblEquipmentDetails Values(1026, 42, 2, 'P4 Dual Core')
Select numEquipmentID As EquipmentID, [Monitor Size], [Processor Type]
From
(Select a.numEquipmentID, txtManufacturer, txtModel, txtFieldName, txtFieldValue
From @tblEquipment a JOIN
@tblEquipmentDetails b ON
a.numEquipmentID = b.numEquipmentID
JOIN @tblEquipmentFields c ON
b.numFieldNameID = c.numFieldNameID
) As SourceTable
Pivot
(
Max(txtFieldValue)
For txtFieldName IN ([Monitor Size], [Processor Type])
) As PivotTable






What I'm wondering is if it's possible to have the columns change dynamically. For example:

If lets say I only want the record with numEquipmentID of 23 to show I only want its corresponding information to show

EquipmentID ProcessorType
23 P4M

Now lets say that I want to bring back an additional record, like 23 and 26 I would like the columns to change to the following

EquipmentID ProcessorType Monitor Size
23 P4M NULL
26 NULL 17

So in essence a column will be added based on the equipmentID. Thanks in advanced.

View 4 Replies View Related

Invert Rows And Columns (PIVOT)

Jul 10, 2006

Hi everybody.

I have this table that contains how many items were sold (and their value) into two departments:



SellerID(PK)
Year(PK)
ItemsSoldDPT1
ItemsSoldDPT2
ValueSoldDPT1
ValueSoldDPT2

1
2002
10
20
300.00
400.00

1
2003
13
71
450.00
320.00

1
2004
8
4
350.00
640.00

1
2005
2
15
110.00
680.00

2
2001
3
1
130.00
100.00

2
2005
1
7
190.00
200.00

2
2006
6
9
170.00
500.00

...
...
...
...
...
...

I'm trying to write a query that puts the data present in the "Year" column as if they were in a row (column definitions)..making sums of pieces and values.. or.. to be more clear..

I want to obtain this:



SellerID
2001 Items
2001 Values
2002 Items
2002 Values
2003 Items
2003 Values
2004 Items
2004 Values
2005 Items
2005 Values
2006 Items
2006 Values
...

1
(NULL)
(NULL)
30
700.00
84
770.00
12
990.00
17
790.00
(NULL)
(NULL)
...

2
4
230.00
(NULL)
(NULL)
(NULL)
(NULL)
(NULL)
(NULL)
8
390.00
15
670.00
...

...
...
..
...
...
...
...
...
...
...
...
...
...
...

Any ideas? I think I should use the PIVOT keyword to write the sql but I can't figure how it works and how can I do that sums.

Please note: I don't know how many distinct values of "Year" exists and the min and max year can be specified by the user.

Thank you for your help.

View 5 Replies View Related

Grouping Columns In A Varaiable When Using PIVOT?

Jan 23, 2008


Hi,
I just read on web that we can not use grouping columns in a variable when using PIVOT operator. For example like,
USE AdventureWorks
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] ) // cannot put these in a variable like @Col
) AS pvt
ORDER BY VendorID;



Though it can be achieved using when making the query using dynamic sql. If some can make it clear why it is possible using dynamic sql and not with the above code.

Regards,

View 4 Replies View Related

Transact SQL :: Duplicate PIVOT Function In 2000

Jul 1, 2015

I have a query that uses the PIVOT function and works fine in SQL 2012.  I've been asked to move the query to a database that has the compatibility level set to 80(SQL 2000).  I receive an "Incorrect syntax near" error when I try to excute the query on the SQL 2000 database.  I would like to duplicate the exiting PIVOT functionality in SQL 2000.The existing query retrieves employee names and the order that the employee should be displayed from a table.  The names will appear on the report according to the order that is retrieved from the database.  Also, the users have requested that only 5 names appear on each row of the report.  This is why the PIVOT function was needed.  Below is an example of how the existing query works.

Table
CREATE TABLE [dbo].[EmpGuest](
 [Guest_ID] [int] NOT NULL,
 [Guest_Name] [varchar](80) NULL,
 [Display_Order] [int] NULL
) ON [PRIMARY]

[code]....

View 4 Replies View Related

Transact SQL :: Add A Grand Total To Pivot Output?

Oct 9, 2015

I have a SP that will generate a pivot output. I want to add a grand total at the end row to sum up the counts for each column.

the SP is as below :

/* COLUMN HEADERS*/
DECLARE
@columnHeaders NVARCHAR (MAX)
SELECT
@columnHeaders  =  COALESCE ( (@columnHeaders)  + ',[' + [Date] + ']', '[' + [Date] + ']')

[code]....

I am getting the below error:

Invalid column name 'Grand Total'.

Msg 205, Level 16, State 1, Line 16

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

View 10 Replies View Related

Transact SQL :: Order To Make A Pivot Dynamically

Jun 9, 2015

I am trying to find a solution in order to make a pivot dynamically. One of my department charge every month all the sales figure in one table and I need to pick up the last two months archived in order to make a pivot and to see if something is changed or not. What I am trying to do is to have these last two months dynamically. create table forum (customer varchar (50), nmonth varchar(6), tot int, archived datetime)

insert into forum values ('Pepsi','201503',100,'2015-04-28'),
('Pepsi','201504',200,'2015-04-28'),
('Texaco','201503',600,'2015-04-28'),
('Texaco','201504',300,'2015-04-28'),

[code]...

As you can see I have to change manually the values underlined every months but it's a temporary solution. How can I set up the last two months in a dynamic way?

View 3 Replies View Related

Transact SQL :: Sum Time Column After Pivot Query

May 18, 2015

i have a table like below,

CREATE TABLE #ATTTABLE
(
Name VARCHAR(20),
AttDate DATE,
PresntTime TIME

[code]....

and then i pivot table by date as column wise using the below query and also displays total time by rowswise

SELECT t1.*, t2.Total
FROM (
SELECT  name,[2015-08-01],[2015-08-02]
FROM (
SELECT  name, AttDate,PresentTime 

[code]....

now what i need is to display sum of time at last row as well, means total time of against date

View 16 Replies View Related

Pivot Results With Columns For Movements In Period

Mar 7, 2014

I have a query which returns the movements to and from our warehouse stock, as well as the current stock for each depot and how much is on order. What I need is a kind of pivot so that each item is shown just once, and then summarises the movements in 4 extra columns: Last 30 days, 30-60 days, 60-90 days and 90-120 days. How can I achieve this with my query below? A sample of some of the results is also shown.

select
iv.item,
iv.descr,
ts.loc_total_on_hand [Stock],
ts.loc_code [Depot],
po.qty [On Order],
po.office [Order Depot],

[Code] .....

View 2 Replies View Related

PIVOT Multiple Columns In Test Table

Apr 24, 2014

-- Here's a test table where I'm trying to workout how to Pivot more than one column.

-- Drop the Temp Table
IF (SELECT Object_id('tempdb..#Test_Pivot_Example')) <> 0
BEGIN
DROP TABLE #Test_Pivot_Example
END

[Code] ....

Once I have worked this out then I need to dynamic populate the IN ([1] etc with the val;ue sin field [SIZE])

but one step at a time trying to workout pivot on more than one column.

View 4 Replies View Related

PIVOT With Dynamic Columns Names Created

Aug 3, 2007

I am trying to do a PIVOT on a query result, but the column names created by the PIVOT function are dynamic.

For example (modified from the SQL Server 2005 Books Online documentation on the PIVOT operator) :

SELECT
Division,
[2] AS CurrentPeriod,
[1] AS PreviousPeriod
FROM
(
SELECT
Period,
Division,
Sales_Amount
FROM
Sales.SalesOrderHeader
WHERE
(
Period = @period
OR Period = @period - 1
)
) p
PIVOT
(
SUM (Sales_Amount)
FOR Period IN ( [2], [1] )
) AS pvt

Let's assume that any value 2 is selected for the @period parameter, and returns the sales by division for periods 2 and 1 (2 minus 1).

Division CurrentPeriod PreviousPeriodA 400 3000 B 400 100 C 470 300 D 800 2500 E 1000 1900

What if the value @period were to be changed, to say period 4 and it should returns the sales for periods 4 and 3 for example, is there a way I can change to code above to still perform the PIVOT while dynamically accepting the period values 4 and 3, applying it to the columns names in the first SELECT statement and the FOR ... IN clause in the PIVOT statement ?

Need a way to represent the following [2] and [1] column names dynamically depending on the value in the @period parameter.

[2] AS CurrentPeriod,
[1] AS PreviousPeriod

FOR Period IN ( [2], [1] )

I have tried to use the @period but it doesn't work.

Thanks in advance.

Kenny

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved