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


ADVERTISEMENT

Transact SQL :: Get Table And Column Name In Separate Column Using PIVOT

Jul 16, 2015

Is there a way we can get Table and Column name in separate column using PIVOT or something?Right now what i have is:

Text                                                     QueryPlan             Plan_handle  
         Name         Value

select id,name,Address from person     <showPlznXML...   010101                 Table            Person
select id,name,Address from person     <showPlznXML...   010101                 column         id
select id,name,Address from person     <showPlznXML...   010101                 Table            Person

[code]....

View 26 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 :: Renaming Pivot Table Column Names Dynamically

Oct 30, 2015

I got a table which gets populated by stor proc where we pivot the Sum(Balance of mortgage) by YYYYMM for the whole duration of the loan term.

I have a requirement to rename the column header where the previous month end balance period be renamed to P0.

if we run the report today, then the balance as at 31/09 should show under column P0 which now shows under 201509 and then P0 keeps shifting with each month run.

How do I dynamically rename the column headers.

View 7 Replies View Related

Transact SQL :: Converting 24hrs Time To 12 Hours Time Query Format?

Apr 21, 2015

SELECT 
    CONVERT(VARCHAR(10),attnc_chkin_dt,101) as INDATE,
    CONVERT(VARCHAR(10),attnc_chkin_dt,108) as TimePart
FROM pmt_attendance

o/p
indate   04/18/2015
time part :17:45:00

I need to convert this 17:45:00 to 12 hours date format...

View 8 Replies View Related

Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?

Aug 3, 2015

How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?

View 5 Replies View Related

Transact SQL :: Create PIVOT Query In Server 2005

Jul 24, 2015

I have a query which I want to convert It PIVOT query

SELECT     Parties.AreaID, Parties.Area, CashSalesDetail.ProductID, CashSalesDetail.ProductName, SUM(CashSalesDetail.Qty) AS QtyFROM         CashSalesDetail INNER JOIN                      CashSales ON CashSalesDetail.CSNo = CashSales.CSNo INNER JOIN                      Parties ON CashSales.PartyID = Parties.PartyIDWHERE     (CashSales.TransDate >= CONVERT(DATETIME, '2014-07-01 00:00:00', 102)) AND (CashSales.TransDate <= CONVERT(DATETIME, '2015-06-30 00:00:00', 102))GROUP BY Parties.AreaID, Parties.Area, CashSalesDetail.ProductID, CashSalesDetail.ProductName

following is my requirement after summing up qty of each area

ProductName      area a         area b       area c
abc                          10                0               
20
def                           1                 
4               2
ghi                           5                 
3               10
jkl                             7                
15              3

Note: numeric values are Quantity of each product in each area

View 15 Replies View Related

Transact SQL :: Create A Temp Table On Results Of A Pivot Query?

Jun 17, 2015

I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.

IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL
DROP TABLE #Pyr
GO
SELECT
vst_int_id,
[4981] AS Primary_Ins,
[4978] AS Secondary_Ins,

[code]....

The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.".   The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT".  I will post the definitions from another effort below.

CREATE TABLE #Pyr
(
vst_int_idINTEGERNOT NULL,
--ivo_int_idINTEGERNOT NULL,
--cur_pln_int_idINTEGERNULL,
--pyr_seq_noINTEGERNULL,

[code]....

SQL Server 2008 R2.

View 3 Replies View Related

Transact SQL :: Dynamic Query For Multi-Header CrossTab Pivot

May 7, 2015

Have a table with a structure like:

FIELD_A
FIELD_B
FIELD_C
FIELD_D
AMOUNT

DATA_A1
DATA_B1
DATA_C2
DATA_D2
37540

[Code] ....

In such a case, devising a dynamic cross-tab query, to cover all the items, and result like a pivot like the following to represent the data in a multi-header pivot like following:

DATA_C1
DATA_C1
DATA_C1
DATA_C2
DATA_C2
DATA_C2

[Code] ....

View 5 Replies View Related

Transact SQL :: Create A Pivot Query That Will Give Result That Is Horizontal

Jul 8, 2015

I have some data which is vertical...I want to create a pivot query in SQL that will give me a result that is horizontal like this. I cannot find a way of doing it without lots of IF or CASE statements?

View 10 Replies View Related

Transact SQL :: Time Column With Order Wise

Nov 17, 2015

I have a table like below

CREATE table #TempTable (ID integer,CTime time(7),CType Varchar(1))
insert into #TempTable VALUES (1001,'16:50:05.0000000','I')
insert into #TempTable VALUES (1001,'13:27:49.0000000','O')
insert into #TempTable VALUES (1001,'20:44:00.0000000','O')
insert into #TempTable VALUES (1001,'21:12:00.0000000','O')

I need result like below screen shot 

here 'I' stands for In,and 'O' for Out, in this example first In time not available

View 7 Replies View Related

Transact SQL :: Total Difference Time In Last Column?

May 23, 2015

I have the below query

DECLARE @GivenDate DATE='2015-05-13'
create table #table (LedgerID int,AttDate Date, checkedtime time,checkedtype varchar(1))
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','09:01:48.0000000','I')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','13:05:52.0000000','O')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','14:10:25.0000000','I')

[code]....

the result is like below

i need 'TotalDiffernceTime' column as new column (OUT1-IN1)+(OUT2-IN2).

i am using SQLServer 2008 R2

View 8 Replies View Related

Transact SQL :: Getting Time Difference Between Same Column In Different Rows

Jun 21, 2015

I have a table data like below

id         type      timestamp
1001    start1    10:34:23:545
1001    start2    10:34:24:545
1001    end2     10:34:24:845
1001    end1     10:34:25:545
1002    start1    10:34:25:645
1002    start2    10:34:25:745
1002    end2     10:34:25:945
1002    end1     10:34:25:965

I need the result as follows

id              millisecond diff start1end1                 millisecond diff start2end2
1001    end1 timestamp-start1 timestamp    end2 timestamp-start2 timestamp
1002    end1 timestamp-start1 timestamp    end2 timestamp-start2 timestamp

SQL Server 2008 R2

View 5 Replies View Related

Transact SQL :: How To Set Case Sensitivity Of A Column In Design Time

Aug 3, 2015

With Sql Server Management Studio, while creating/modifying a table I want to specify one of its columns to store values in lower case only. Can it be done through the designer or by some other means?

N.B.: Using SQL Server 2005.

View 22 Replies View Related

Pivot Query - Assigning Row Value Based On Column Name

Sep 27, 2007



Hey all,



i have a question for all the SQL Gurus out there. I have tried to think of a way around, it, but i cannot work it out.



I have a set of data: Samples Below:



Item Warehouse FOR1 FOR2 FOR3 FOR4 FOR5 FOR6 FOR7 FOR8 FOR9 FOR10 FOR11 FOR12 FOR13 FOR14

01-0001 010 329 329 335 343 317 331 328 331 31





I have written a Query to Pivot this data like below:



SELECT WAREHOUSE,ITEM, QTY

FROM

(SELECT ITEM,WAREHOUSE,FOR1,FOR2,for3,for4,for5,for6,for7,for8,for9,for10,
for11,for12,for13,for14,for15,for16,for17,for18,for19,for20,for21,
for22,for23,for24 FROM mvxreport.tbldmsForecasttoMovex) p

UNPIVOT

(QTY FOR tbldmsForecasttoMovex IN (FOR1,FOR2,for3,for4,for5,for6,for7,
for8,for9,for10,for11,for12,for13,for14,for15,for16,for17,for18,for19,
for20,for21,for22,for23,for24))AS unpvt





Warehouse Item Qty
010 01-0001 329
010 01-0001 329
010 01-0001 335
010 01-0001 343
010 01-0001 317
010 01-0001 331
010 01-0001 328
010 01-0001 331
010 01-0001 315
010 01-0001 344
010 01-0001 334
010 01-0001 321
010 01-0001 327
010 01-0001 328
010 01-0001 332
010 01-0001 342
010 01-0001 316
010 01-0001 330
010 01-0001 330
010 01-0001 331
010 01-0001 315
010 01-0001 343
010 01-0001 333
010 01-0001 322




I would like to add some more code to the query, so for each FOR% column,
i can put a numeric value in it. The value will be the numbers ,1 - 24 . One for each line as this represents Months Forward.



Example:



Warehouse Item Qty Month
010 01-0001 329 1
010 01-0001 329 2
010 01-0001 335 3
010 01-0001 343 4
010 01-0001 317 5
010 01-0001 331 6
010 01-0001 328 7
010 01-0001 331 8
010 01-0001 315 9
010 01-0001 344 10
010 01-0001 334 11
010 01-0001 321 12
010 01-0001 327 13
010 01-0001 328 14
010 01-0001 332 15
010 01-0001 342 16
010 01-0001 316 17
010 01-0001 330 18
010 01-0001 330 19
010 01-0001 331 20
010 01-0001 315 21
010 01-0001 343 22
010 01-0001 333 23
010 01-0001 322 24






Does anyone know how i can do this?



Many Thnank

Scotty



View 5 Replies View Related

Transact SQL :: How To Get Time Portion Only Of A Date For LINQ Query

Aug 4, 2015

I'm trying to translate this portion of VFP code into LINQ query:

select COUNT(ID) as conflicts
from dbo.max4sale where <<thisform.cWhere>>
AND Start_Time >= <<VFP2SQL(m.ltBegin + m.lnStartTime)>>
and Start_time <= <<VFP2SQL(m.ltEnd)>>
AND CONVERT(varchar(5),Start_Time,108) <= <<VFP2SQL(m.lcEndTime)>>
AND CONVERT(varchar(5),End_Time,108) >= <<VFP2SQL(m.lcStartTime)>>
<<m.lcDays>>

Here is my non-working attempt:

var startTime = new DateTime(1900, 1, 1, beginDateTime.Hour, beginDateTime.Minute, 0);
var endTime = new DateTime(1900, 1, 1, endDateTime.Hour, endDateTime.Minute, 0);
var daysOfWeek = dailyLimits.Where(dl => dl.Selected == true).Select(ds => ds.WeekDay).ToList();
if (daysOfWeek.Count() < 7) // not all days of the week selected

[Code] .......

First of all, I see a bug in my logic now as the first part of the query I need to do all the time and only the second part if the count < 7. But that's not my problem - I can not figure out how to make times comparison only using LINQ. Ideally I think I'd like to have cast(start_time as time) >= @p1 as a result to be executed by LINQ.

BTW, I am only getting the error in run-time that Parse can not be interpreted. So, I need to figure out another way of making LINQ recognize my intent of checking time portion of the date only.

View 9 Replies View Related

Transact SQL :: SELECT On Column Name From Query Result Set In Same Query?

May 9, 2015

I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).

To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:

COLUMN_NAME Value
----------- -----
colB        123
colA        XYZ

I've tried dynamic SQL to no success, probably not executing the concept correctly...

Below is what I have:

CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC

[Code] ....

View 4 Replies View Related

Transact SQL :: Create Index On Temp Table To Reduce Run Time Of Update Query

Apr 29, 2015

I want to create index for hash table (#TEMPJOIN2) to reduce the update query run time. But I am getting "Warning!

The maximum key length is 900 bytes. The index 'R5IDX_TMP' has maximum length of 1013 bytes. For some combination of large values, the insert/update operation will fail". What is the right way to create index on temporary table.

Update query is running(without index) for 6 hours 30 minutes. My aim to reduce the run time by creating index. 

And also I am not sure, whether creating index in more columns will create issue or not.

Attached the update query and index query.

CREATE NONCLUSTERED INDEX [R5IDX_TMP] ON #TEMPJOIN2
(
[PART] ASC,
[ORG] ASC,
[SPLRNAME] ASC,
[REPITEM] ASC,
[RFQ] ASC, 

[Code] ....

View 7 Replies View Related

Transact SQL :: Query To Create New Column

Sep 2, 2015

I have a data with mutliple esn but different auditdate and opid. I will pull this data filtering by date and opid. My requirements is not to include the opid = 51 but need to get the desired opdesc for this esn that contains opid=51.

See below sample ddl and desired result. I dont want do include the opid = 51 because it will create a duplicate in transaction instead retain the opid 5

example: esn T9000000000019829505 has multiple rows with different auditdate and opid. retain the records for opid is equal to 5 but get the opdesc for opid is equal 51.

reate table #test
(esn nvarchar(35), dateaudit datetime, opid int)
insert into #test(esn,dateaudit, opid)values('352452060499834','2015-05-12 20:32:39.490',5)
insert into #test(esn,dateaudit, opid)values('352452060499834','2015-07-06 17:35:14.210',5)
insert into #test(esn,dateaudit, opid)values('T9000000000019829505','2015-01-14 15:18:45.620',5)

[Code] ....

Desired Result:

esn-------------------dateaudit----------------opid--opdesc--rn
352452060499834------2015-05-12 20:32:39.490---5---Shipping--1
352452060499834------2015-07-06 17:35:14.210--5---Shipping--1
T9000000000019829505--2015-01-14 15:18:45.620--5---Scrap-----1

OR

esn-------------------dateaudit----------------opid--opdesc--rn--remarks
352452060499834------2015-05-12 20:32:39.490---5---Shipping--1---shipping
352452060499834------2015-07-06 17:35:14.210--5---Shipping--1---shipping
T9000000000019829505--2015-01-14 15:18:45.620--5---Shipping--1---Scrap

View 5 Replies View Related

Transact SQL :: Run A Query That Checks A Column To See If Its Numeric

Jul 14, 2015

I am trying to run a query that checks a column to see if its numeric. If it is, it should cast as a float, if its not numeric it should return 0.

select case when (isnumeric(Ref)=1) THEN select (cast(Ref,float) )
else (0) as outputfrom #table

but its returning an error.

View 5 Replies View Related

Transact SQL :: Query Works Even If Column Not Exists In Subquery

Jul 23, 2015

When I execute the below queries it works perfectly where as my expectation is, it should break.

Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1)
In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment.  If I do change the query to something below then definately it will break -
Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)

Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.

create table TestDepartment
(
DeptId int identity(1,1) primary key,
name varchar(50)
)
create table ChildDepartment
(
Id int identity(1,1) primary key,

[Code] ....

View 3 Replies View Related

Transact SQL :: Update Time Portion Of DateTime Field With Time Parameter

Oct 3, 2015

I hope to update a DateTime column value with a Time input parameter.  Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000

ALTER PROCEDURE [dbo].[SendEditUPDATE]
@QuePoolID int=null
,@ApptTime time(7)
,@SendOnDate datetime

[code]...

View 14 Replies View Related

How To Create Key Time Column And Key Column For A Case Table And A Nested Table For Time Series Algorithm?

Jun 18, 2007

Hi, all experts here,



Thanks for your kind attention.



I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?

Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,



View 6 Replies View Related

Questions On Key Column In Case Table And Key Time Column In Nested Table Using Time Series Algorithm

Jun 4, 2007

Hi, all experts here,



Thank you very much for your kind attention.



I am confused on key column of case table and key time column of nested table by using Time Series algorithm.

In my case, the case table structure is as below:

Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks),

While the nested table structure is as below:

Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID)

So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table?

Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month?



Hope it is clear for your advices and help.

And I am looking forward to hearing from you shortly.



With best regards,



Yours sincerely,





View 1 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

Column Data To Column Heading By Dynamic Pivot Maybe

Feb 27, 2008



Hi there,
I am a new member of this site and I am not very much aware of T-sql's working.
My question is what if I need to get one column's data to be the heading of another column.
To be very exact I have a school's database. The table I am talking about is of the results of students. The table contains Student ID, Subject ID, Total marks of the subject, Marks obtained in the subject. Now I want to print a report by generating data from this table. Right now the data is something like this
StuID - - - SubID - - - -Tot - - -Obt
1 - - - - - - -1 - - - - - - -50 - - - 38
1 - - - - - - -2 - - - - - - -50 - - - 41
1 - - - - - - -3 - - - - - - -50 - - - 42
1 - - - - - - -4 - - - - - - -50 - - - 40
2 - - - - - - -1 - - - - - - -50 - - - 35
2 - - - - - - -2 - - - - - - -50 - - - 40
2 - - - - - - -3 - - - - - - -50 - - - 42
2 - - - - - - -4 - - - - - - -50 - - - 41

StudentID and SubjectID fields are related to other tables so I can get the names from there but when I need the report I need the data in the form of
StuID - Sub 1 - - - Sub 2 - - - Sub 3 - - - -Sub4
1 - - - - 38 - - - - - - 41 - - - - - - 42 - - - - - - 40
2 - - - - 35 - - - - - - 40 - - - - - - 42 - - - - - - 41

The Subjects can be different for different students so the query should be dynamic instead of hard coding the names of the subjects. I hope I am clear with my question. The subjectIDs or their names will become the headings and they will contain the obtained marks for that subjects in their columns just for the reports. I have also checked the PIVOT function but was not able to do what I wanted.
Thanks.

View 9 Replies View Related

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 View Related

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

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

Pivot Data Order By Result Time

Apr 17, 2015

I am querying a table with multiple rows

SELECT caseId, Name ,CompletedDate
FROM TableZ

the number of results per CaseID are varable some have 1 record others up to 36 records..I need to pivot the results based off of the competed date
Name has 20 distinct values but we can have diplicates...so something like

Case Name Date
1 This 2015-01-01
1 That 2015-01-03
2 That 2013-01-01
2 Then 2014-12-03
2 This 2015-01-01
2 There 2015-04-03
3 This 1999-01-01
3 That 2001-01-02
3 This 2006-01-01

looks like this

1 This That
2 That Then This There
3 This That This

View 2 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







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