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
ADVERTISEMENT
Mar 26, 2014
I have a query
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(name)
[Code] ....
and so on.
I've tried putting my group by everywhere but it's not working. I'm missing something but what?
View 9 Replies
View Related
Mar 21, 2006
Hi all.
I moved my OLAP DB to SQL Server 2005, installed Microsoft OLE DB Provider for Analysis Services 9.0 on the client and keep creating Pivot Tables in Excel until...
Grouping - when I try to group couple of members (or even 1 for that matter) I'm getting :
"Intrnal error: An unexpected error occurred (file"mdinfo.cpp", line 3335 function "MDInfo::Init")."
The only reference to mdinfo error I found in OLAP newsgroup where a guy was getting it trying to process the cube. My cube is processed (obviuosly). So, anybody knows what has changed with respect to grouping in PTS?
Michael
View 13 Replies
View Related
Jul 17, 2007
Hi! I am trying to do something that I would think should be rather simple.I have a SQLDataSource that has a simple select statement.I have a String Variable st_tempI know my SQLDataSource returns only one value, and it's a string.HOW DO I DO somehitng like st_temp=SQLDataSource.Select;I am getting nothing but errorrs Thanks in advance Dan
View 4 Replies
View Related
Apr 21, 2004
HI,
i have 2 columns named firstname and lastname, i need to get them into 1 column named name with a space between them.
Does anyone have a tip to do this?
Wimmo
View 2 Replies
View Related
Jul 23, 2005
Hi,I was trying to retrieve some data in such a way that it 2 columns willbe merged into one, with a column in between. I am trying to dosomething like this:SELECT LastName + ", " + FirstName AS NameFROM EmployeeTBLORDER BY LastNameBut SQL Server does not like this syntax (though it does work with"LastName + FirstName").I appreciate any help.Thanks,Aaron
View 4 Replies
View Related
Jul 30, 2007
Hello guys !!
I'm actually a Mysql user, not a SQL Server user, just becouse the business I work uses it. But the problem I've had I think doesn't have any relation on the plataform it's running...
This is a construction software... I'll try my best to explain you : My table has 4 main columns (Face,Station,Combination,sAs). Face , Station and Combination form a primary key (never repeat together) and the "sAs" represents a calculus between some parameters.
The problem (it became a challenge already :D) consists in selecting the Face,Station and Combination where sAs is maximus, BUT grouping ONLY Face and Station.
For example :
Face Station Combination sAs
F1 0 Comb1 45
F1 0 Comb2 13
F1 0 Comb3 30
---
F1 10 Comb1 42
F1 10 Comb2 60
F1 10 Comb3 12
---
F2 0 Comb1 32
F2 0 Comb2 15
F2 0 Comb3 01
---
F2 10 Comb1 02
F2 10 Comb2 07
F2 10 Comb3 23
Here is the challenge :
If you execute the following query : "select Face,Station,Combination,max(sas) as sAS from test group by Face,Station" it returns you an arbitrary Combination for the rows in the resultset.
Face Station Combination sAs
F1 0 Comb1 45
F1 10 Comb1 60
F2 0 Comb1 32
F2 10 Comb1 23
But the combination I'd like to have is the combination related to the maximus sAs in F1/10, Comb2 instead of Comb1 returned..... The same occurs with the last row F2/10.
The query I wanna find should return the following resultset.
Face Station Combination sAs
F1 0 Comb1 45
F1 10 Comb2 60
F2 0 Comb1 32
F2 10 Comb3 23
Please, if somebody has any idea, share it to help me....
Thanks a lot..
Rodrigo
Some code to make it easier to try.... Maybe it has some difference between SQL Server and MySQL.
////////////// CUT HERE
create table test(
Face varchar(20),
Station int,
Combination varchar(20),
sAs int
);
insert into test values('F1', 0, 'Comb1', 45);
insert into test values('F1', 0, 'Comb2', 3);
insert into test values('F1', 0, 'Comb3', 30);
insert into test values('F1', 10, 'Comb1', 42);
insert into test values('F1', 10, 'Comb2', 60);
insert into test values('F1', 10, 'Comb3', 12);
insert into test values('F2', 0, 'Comb1', 32);
insert into test values('F2', 0, 'Comb2', 15);
insert into test values('F2', 0, 'Comb3', 01);
insert into test values('F2', 10, 'Comb1', 02);
insert into test values('F2', 10, 'Comb2', 07);
insert into test values('F2', 10, 'Comb3', 23);
select Face,Station,Combination,max(sas) as sAS from test group by Face,Station;
////////////// CUT HERE
Rodrigo Bornholdt
View 3 Replies
View Related
Jul 20, 2005
Hi,I have the following SQLSELECT Table1.Col1, Table3.Col1 AS Expr1,COUNT(Table1.Col2) AS Col2_No, COUNT(Table1.Col3) AS Col3_No etc,FROM Table3INNER JOIN Table2 ON Table3.Col1=Table2.Col1RIGHT OUTER JOIN Table1 ON Table2.Col2=Table2.Col2GROUP BY Table1.Col1, Table3.Col1The output rows have a value in either Table1.Col1 or Table3.Col1 but notboth.I'd like to combine Table1.Col1 and Table3.Col1 and group by the combinedcolumn in the result but don't know how.Thanks gratefully
View 5 Replies
View Related
Jan 23, 2015
I am retrieving some data from Invoices, Customers and Companies tables as follows, but would like to make the customerName and the Companies.Name as single column such Name and similarly for customerID/companyID and customerCode/companyCode.
Code:
with cte
as
(
selectdistinct i.invoiceNumber, itemID, customers.customerID, Companies.companyID
,SUM(net_weight) as totalWeight, rate
,(select SUM(net_weight) * rate) as amount
[code]....
View 6 Replies
View Related
Oct 7, 2015
I am using sql table named as product which is having columns partno,partnm,weight,surfacearea,totalhr,type
I want sum of weight,surfacearea,totalhr and grouping on partno,partnm,type
If I use query select partno,partnm,sum(weight),sum(surfacearea),sum(totalhr) from product GROUP BY partno,partnm then its working correctly with sum and grouping but if I use query select partno,partnm,sum(weight),sum(surfacearea),sum(totalhr),type from product GROUP BY partno,partnm,type then it is not grouping as expected.
why if third column included in group by clause its not working correctly...Is there any way to group as I want.
View 9 Replies
View Related
Sep 15, 2015
I have table like below.
filename col1 col2 col3
ABD Y NULL Y
XYZ Y Y Y
CDZ Y Y Y
I Need a output like this
filename col1 col2 col3 Group
ABD Y NULL Y Group1
XYZ Y Y Y Group2
CDZ Y Y Y Group2
I wanted to group the col1 , col2, col3 and group it as same group.
View 3 Replies
View Related
Sep 22, 2007
Hi All,
Thanks for dropping by my post.
I have a table which is of this form.
ID
MS030_A
MS030_F
MS036_A
MS036_F
MS040_A
MS040_F
ZZ0023
2/16/06
2/16/06
8/10/07
8/10/07
11/21/05
11/21/05
ZZ0031
8/10/07
4/5/07
8/9/07
8/9/07
3/22/07
3/22/07
ZZ0077
8/9/07
9/7/07
8/10/07
8/10/07
8/10/07
9/7/07
ZZ0078
8/10/07
9/7/07
8/9/07
8/9/07
8/9/07
9/7/07
ZZ0079
8/9/07
8/10/07
10/26/05
10/26/05
8/10/07
8/10/07
ZZ1030
3/31/05
8/10/07
9/1/05
9/1/05
8/9/07
ZZ1033
3/24/06
8/9/07
8/9/07
8/9/07
3/31/05
ZZ1034
8/10/07
8/10/07
8/9/07
8/9/07
3/24/06
ZZ1037
8/9/07
8/9/07
9/24/07
9/24/07
ZZ1040
10/26/05
10/26/05
9/24/07
9/24/07
ZZ1041
9/1/05
9/1/05
9/24/07
9/24/07
ZZ1042
8/9/07
8/9/07
9/24/07
9/24/07
11/21/05
The goal is to group all this transactions by Month and Year.
Something like this....
MS030_A
MS030_F
MS036_A
MS036_F
MS040_A
MS040_F
Month
Year
3
2
2
2
2
2
1
2006
4
4
7
9
8
9
2
2006
10
10
6
8
8
3
2006
4
4
5
5
3
2
4
2006
5
6
8
3
7
1
5
2006
For just one date column it is pretty straight forward i.e., just do a select count and group by DATEPART ( Mm, DateField)
but for multiple columns i am in a total fix....
can please someone help me out...
appreciate your help
View 6 Replies
View Related
Nov 24, 2015
I am using
SSAS: version 2008
Excel: version 2013
I am connecting to SSAS cube from Excel and I have date dimension with 4 fields (I have others but I don't use it for this case). I created 4 fields in order to test all possible scenarios that I could think of:
DateKey:
- Type: System.Integer
- Value: yyyyMMdd
Date:
- Type: System.DateTime
DateStr0:
- Type: System.String
- Value: dd/MM/yyyy (note: I am not using US culture)
- Example: 01/11/2015
DateStr1:
- Type: System.String
- Value: %d/%M/yyyy (note: I am not using US culture)
- Example: 1/11/2015
Filtering on date is working fine:
Initially, in excel, filtering on date was not working. But after changing dimensional type to time, and setting DataType to Date, as mentioned in [URL] filter is working fine as you can see in the picture.Grouping on date is not working:
I have hierarchy in my Date dimension and I can group based on hierarchy, no problem. But user is used to pre-build grouping function of excel, and he wants to use that. Pre-build functions of Excel, Group and ungroup seems to be available as you can see in following picture:
But when user clicks 'Group', excel groups it as if it is a string, and that is the problem. User wants to group using pre-build grouping function available in Pivot table. I also find out that Power Pivot Table does not support this excel grouping functionality. And if I understood well, this pre-build grouping functionality of excel, needs to do calculation at run time, and that is not viable solution if you have millions of rows. So Power pivot table does not support pre-build grouping functionality of excel and hence we need to use dimension hierarchy to do the grouping. But I am not using Power Pivot table, I am using simple Pivot Table. So I expect grouping functionality to be working fine. Then I tried to do simple test. I created a simple data source in excel itself. And use it as source of my Pivot table. Then grouping is working fine. The only difference that I can see is (When double click the Measure value in Excel),For date values of my simple test, excel consider them as 'Date'.
For date values of my data coming from cube, excel consider them as 'General'
2.1. But value here is same as it was in simple test.
2.2. 'Date Filter' works just fine.
2.3. If I just select this cell and unselect it, then excel change type to 'Date' though for that cell.
2.4. I have created 4 different types of fields in my date dimension thinking that values of attribute of my dimension might be the problem, but excel consider 'General' for all of them.
2.5 This value (that can be seen when double clicking on measure) comes from 'Name Column' of the attribute. And the DataType defined is WChar. And I thought that might be the reason of issue. And I changed it to 'Date'. But SSAS does not allow it to change to 'Date' giving error : The 'Date' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.
So, I don't know, what is the puzzle piece that I am missing.
1. Date filter works, group does not work
2. Excel consider it as 'General' string.
3. SSAS does not allow to change 'NameColumn' to Date.
View 2 Replies
View Related
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
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
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
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
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
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
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
Sep 1, 2015
I want to develop a ssrs report which is grouped by month?
View 4 Replies
View Related
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
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
View Related
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
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
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
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
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
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
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
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
Jun 26, 2006
I have something of a challenge for you folks (at least it's very challenging for me).
I have a table that has data that looks like this:
Date______OrderNum____WorkDay
2006-06-1__AA_________1
2006-06-1__AB_________1
2006-06-2__BA_________2
2006-06-2__BB_________2
2006-06-2__BC_________2
2006-06-5__CA_________3
2006-06-5__CB_________3
etc.
So, there are dates that orders happened and each date is marked as the 1, 2, 3, etc. business day of the month.
What I am trying to write is a SQL statement that will display results thus:
Day1______Day2______Day3_______etc.
AA________BA_______CA_________etc.
AB________BB_______CB_________etc.
Is this making any sense to anyone? Basically, I need to turn the WorkDay rows into columns and display all the info for each WorkDay under them.
I have a feeling this isn't hard, but I am fairly new to writing SQL so any advice would be pure gold to me. Thanks!
View 7 Replies
View Related
May 16, 2007
Hi,
I have the following data in a table called "Products" :
Product Qty LHinging RHinging
-------- ----- -------- --------
Panel_1 1 R
Panel_1 1 L
Panel_1 1 R
Panel_1 1 R
Panel_2 1
Panel_2 1 L
I need to group this data like this :
Product Qty LHinging RHinging
-------- ----- -------- --------
Panel_1 3 L R
Panel_2 1
Panel_2 1 L
How can I do that ???
Thanks !
View 3 Replies
View Related