Pivoting A Results Table MSSQL
Nov 12, 2007
I have a query in which I would like to pivot the results
I presently have my results displaying something like this.
OrderNumber Product OrderQuantity
--------------- --------------- ----------------------
0608 Prod1 3
0608 Prod2 12
0608 Prod3 2
What I am after is for the results to display something like this.
OrderNumber Prod1 Prod2 Prod3
--------------- --------- --------- ---------
0608 3 12 2
This is using SQL Server ver 8.0
View 3 Replies
ADVERTISEMENT
Jan 28, 2008
Hello there I have a table like so
Q1 Q2 Q3 Q4 Sales Rep
1 5 6 0 John
2 3 6 0 Rod
3 2 3 0 Gill
4 5 1 0 Guy
And I would like to rearrange it like
John Rod Gill Guy
Q1
Q2
Q3
Q4
Is this something I can accomplish with the PIVOT or UNPIVOT commands? Is there another way?
View 9 Replies
View Related
May 7, 2008
Hi ,
I want to pull data from XLs file and put them in a table.
Source file looks like this.
Month
Mar-07
Apr-07
May-07
Non-Accruals
$304,732,515
$307,051,978
$308,274,921
REO
$115,072,839
$123,957,394
$149,744,174
Home Equity Total NPA
$419,805,354
$431,009,372
$458,019,095
Destination table should look like this.
Date(Only the first row ) in XL file is in the following format.
DATE(YEAR($O1)-1,MONTH($O1)-1,DAY($O1)).
From second row onwords data format is Money type.
I hope I need to convert the date row into SQL datetime type too. Otherwise it comes as NULL.
Month
Non-Accruals
REO
Home Equity Total NPA
Mar-07
$ 304,732,515
$ 115,072,839
$ 419,805,354
Apr-07
$ 307,051,978
$ 123,957,394
$ 431,009,372
May-07
$ 308,274,921
$ 149,744,174
$ 458,019,095
Can i create a SSIS package to do this job? if so , How? I'm not sure which transformation should i used and how?
Hope some one can help me
Thanks
View 5 Replies
View Related
Sep 21, 2007
hello guys !!!
i have a table as
id date data
1 3/10/2007 "hello"
1 4/10/2007 "hi"
2 3/10/2007 "hello"
2 4/10/2007 "why"
i need the output like
id 3/10/2007 4/10/2007
1 "hello" "hi"
2 "hello" "why"
Any idea ???
any means to do it??
View 5 Replies
View Related
May 7, 2008
Hi ,
My source file looks like this.
Month
Mar-07
Apr-07
May-07
Non-Accruals
$304,732,515
$307,051,978
$308,274,921
REO
$115,072,839
$123,957,394
$149,744,174
Home Equity Total NPA
$419,805,354
$431,009,372
$458,019,095
Destination table should look like this.
Month
HE Non-Accruals
HE REO
Home Equity Total NPA
Mar-07
$ 304,732,515
$ 115,072,839
$ 419,805,354
Apr-07
$ 307,051,978
$ 123,957,394
$ 431,009,372
May-07
$ 308,274,921
$ 149,744,174
$ 458,019,095
Can anyone help me to write t-sql code to transfer data into destination table as its shown above.
Thanks
View 1 Replies
View Related
Apr 25, 2008
I've done both a CTE and a pivot, but never together. I did see a few examples out there and followed them, but mine isn't working. I have four 'tables' within the CTE, and then my final select statement joins all of them and attempts to pivot. My error is:
Msg 156, Level 15, State 1, Line 90
Incorrect syntax near the keyword 'PIVOT'.
WITH TwoYrsActual (LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,Week_Idx, LocationType_Code, Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,"06Actual")
AS (
SELECT DISTINCT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD,
ActualSalesAmtUSD AS '06Actual'
FROM dbo.FactInventoryHistory HIS
INNER JOIN DimLocation LOC ON
His.LocationType_Code = Loc.LocationType_Code
INNER JOIN DimDate Date ON
His.Week_idx = Date.Date_Idx
INNER JOIN DimScenario Scenario ON
His.Scenario_Idx = Scenario.Scenario_Idx
WHERE Scenario_Code = 'FY06A'
GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,
ActualSalesAmtUSD
),
OneYearActual (LocationType_Name, FiscalYear_Name,FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,Week_Idx, LocationType_Code, Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,
"07Actual")
AS (
SELECT DISTINCT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD,
ActualSalesAmtUSD AS '07Actual'
FROM dbo.FactInventoryHistory HIS
INNER JOIN DimLocation LOC ON
His.LocationType_Code = Loc.LocationType_Code
INNER JOIN DimDate Date ON
His.Week_idx = Date.Date_Idx
INNER JOIN DimScenario Scenario ON
His.Scenario_Idx = Scenario.Scenario_Idx
WHERE Scenario_Code = 'FY07A'
GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,
ActualSalesAmtUSD
),
PresentYrActual (LocationType_Name,FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,Week_Idx, LocationType_Code, Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,
"08Actual")
AS (
SELECT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD,
ActualSalesAmtUSD AS '08Actual'
FROM dbo.FactInventoryHistory HIS
INNER JOIN DimLocation LOC ON
His.LocationType_Code = Loc.LocationType_Code
INNER JOIN DimDate Date ON
His.Week_idx = Date.Date_Idx
INNER JOIN DimScenario Scenario ON
His.Scenario_Idx = Scenario.Scenario_Idx
WHERE Scenario_Code = 'FY08A'
GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,
ActualSalesAmtUSD
),
PresentYrPlanned (LocationType_Name,FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,Week_Idx, LocationType_Code, Scenario_Idx, Scenario_Code, PlannedTY,
ActualSalesAmtUSD)
AS (
SELECT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD AS PlannedTY,
ActualSalesAmtUSD
FROM dbo.FactInventoryHistory HIS
INNER JOIN DimLocation LOC ON
His.LocationType_Code = Loc.LocationType_Code
INNER JOIN DimDate Date ON
His.Week_idx = Date.Date_Idx
INNER JOIN DimScenario Scenario ON
His.Scenario_Idx = Scenario.Scenario_Idx
WHERE Scenario_Code = 'FY08P'
GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,
ActualSalesAmtUSD
)
select * from (
SELECT PresentYrActual.LocationType_Name, PresentYrActual.FiscalPeriodOfYear, PresentYrActual.FiscalWeekOfPeriod, PresentYrActual.FiscalWeekOfYear, PresentYrActual.LocationType_Code, PresentYrActual.Scenario_Idx, PresentYrActual.Scenario_Code, "08Actual", "07Actual", "06Actual",PlannedTY
FROM PresentYrActual
FULL OUTER JOIN OneYearActual ON
PresentYrActual.FiscalWeekOfYear = OneYearActual.FiscalWeekOfYear AND
PresentYrActual.LocationType_Code = OneYearActual.LocationType_Code
FULL OUTER JOIN TwoYrsActual ON
PresentYrActual.FiscalWeekOfYear = TwoYrsActual.FiscalWeekOfYear AND
PresentYrActual.LocationType_Code = TwoYrsActual.LocationType_Code
FULL OUTER JOIN PresentYrPlanned ON
PresentYrActual.FiscalWeekOfYear = PresentYrPlanned.FiscalWeekOfYear AND
PresentYrActual.LocationType_Code = PresentYrPlanned.LocationType_Code
)
PIVOT
(
SUM(PlannedTY)
FOR PresentYrActual.LocationType_Code IN (C, M))
AS pivottable
View 13 Replies
View Related
Nov 12, 2004
I have a reference table that looks like this
id | value
==========
1,abc
1,def
1,ghi
2,def
2,jkl
I want these values to go horizontally into another table matched on id, to look like this:
id | value
========
1,abc def ghi
2, def jkl
I built a cursor to parse through it but was taking forever (there's 185,000 records in the reference table). Any idea's on the fastest way to perform this function?
View 1 Replies
View Related
Aug 29, 2013
I'm getting nulls on execution of the below query. I have tried both isnull and coalesce, but to no avail.
select *
from
(
select
a.BusinessUnitCode,
a.AdmitCCYYMM As Date, a.[Count of Admits]*1.0 / m.MemberCount * 12000 AdmitsPer1000
[Code] .....
View 3 Replies
View Related
Jan 4, 2006
there is a prblem with data in pivoting the table.
problem is like this--
there is some data 'xy' and some data 'xy '. when i m giving 'xy' as a pivot key value it doesent recognise 'xy ' and viseversa..
i can't reduce the size of the datatype coz there is some data of diffrent size as 'abcd'.
this data is loaded from excel sheet to sql sever table.
wht can i do for this problem.
is there any method to truncate the indivisual data, i m using nvarchar datatype for this.
View 2 Replies
View Related
Jul 20, 2005
gud day.please help me. im working right now on a case study that willretrieve/produce a simple report on sql. my problem is I dont know howto pivot queries like in access. please help me. thanks
View 2 Replies
View Related
Apr 28, 2008
Hi,
Can anybody help me with the following...I want to Pivot the following data
Pcode
Year
Month
Mcode
Value
2
2008
March
EN10A
56349.1
2
2008
March
EN10B
1061.6
2
2008
March
EN10C
2.67
2
2008
March
EN10D
8370
2
2008
April
EN10A
819.31
2
2008
April
EN10B
245.09
2
2008
April
EN10C
33.38
2
2008
April
EN10D
2.31
After Pivot...the data should be like this
Pcode
Year
Month
EN10A
EN10B
EN10C
EN10D
2
2008
March
56349.1
1061.6
2.67
8370
2
2008
April
819.31
245.09
33.38
2.31
Can we use Pivot function or is their a easier way for doing this...Also the MCodes are dynamic so now there are only 4 distinct MCodes but they may be more than four...
View 4 Replies
View Related
Oct 11, 2004
I’d like to get some data which includes month values bound to a data grid. The data is stored in a table like so:
Measure Month Value
A June 10.00
A July 9.00
A Aug 11.00
B Jun 100.00
B Jul 98.00
B Aug 99.00
C Jun 0.75
C Jul 0.8
C Aug 0.91
I need to report the data like this:
Measure Jun Jul August
A 10 9 11
B 100 98 99
C 75% 80% 91%
This was simple in classic ASP. Just use two recordsets, create a new table cell for each month using the first recordset then use the second recordset for each row.
But is there a way to “Pivot� or rotate the data so I can use the DataGrid? It only seems possible if each month has its own column field in table. Each month add a new column.
I can restructure the database, if needed.
I thought about creating a Cube, but that seems to have its own limitations. For example what if I want to add a Column for Quarter and year totals? I don’t think it’s possible to show multiple planes like that in an query of a cube.
It seems that this might be resolved in the presentation layer or the data layer. Any Suggestions?
View 1 Replies
View Related
Jun 4, 2008
I have a table with the following structure
[ID] [A] [B] [Cnt] [Qty]
1 s v1 4 40
2 g v1 2 23
3 p v2 7 22
4 l v3 1 6
5 v v4 7 5
Since I do not know before hand what [B] will be , I have created a dynamic script to pivot the data :
Select *
from ( select [ID],[A],[B],sum([Cnt]) AS Cnt
group by [ID],[A],[B]
) a
PIVOT ( sum(cnt) for [B] in (@list)) b
Now thequstion is :
1. How do I amend this qry to eliminate nulls in my output
[ID] [A] [v1] [v2] ......[vn]
1 s null 9
2 g 10 null
2. Is it possible to include both [cnt] and [Qty] traspose along
[B] = v1,v2 ...... vn
Any advice will be most welcome.
Thanks
View 4 Replies
View Related
Nov 29, 2007
I need some help here in Pivoting the table.
I have the table with the Following Columns.. and here is the sample data. Assume that below table will just have one weeks worth of data.
I can write a stored Proc using cursor, but I just want to learn how
to do it with using cursors
TD-------- Acct------ RouteID----Symbol---- Quantity----
---------- ---------- ---------- ---------- -----------
11/26/2007 40B91209-- CSFB------ GBL--------100
11/26/2007 40B91209-- SIGMA-X----TDY--------100
11/26/2007 4W3L1209-- CSFB------ BIDZ------ 1300
11/26/2007 4W3L1209-- CSFB------ SURW------ 100
11/27/2007 HFS10003-- SIGMA-X----URBN------ 500
11/27/2007 RAM10001-- ISE--------DSCP------ 300
11/27/2007 RAM10001-- SIGMA-X----HYC--------468
11/28/2007 HFS10003-- CSFB------ ARO--------5900
11/28/2007 HFS10003-- CSFB------ CAB--------1300
11/28/2007 HFS10003-- CSFB------ PNRA------ 4600
11/29/2007 RAM10001-- CSFB------ DSCP------ 175
11/29/2007 HFS10003-- CSFB------ CL-------- 220
11/29/2007 WIL10008-- SIGMA-X----CBM--------1400
The output should look some thing like this. If some can help me
AcctNum----RouteID--symbol--MON--TUE--Wed--THU---- FRI
WIL10008---SIGMA-X--CBM-----0----0----0----1400-- 0
Thanks for any help.
View 2 Replies
View Related
Mar 20, 2008
In the following code examples I got to learn PIVOT, I found an error for SUM. However when this is ran against the AdventureWorks db it works fine. Notice it is using a table variable and not an actual table. What do I need to do to my db to get this to work?
Thanks!
declare @sales table
(
[Year] int,
Quarter char(2),
Amount float
)
insert into @sales values(2001, 'Q1', 70)
insert into @sales values(2001, 'Q1', 150)
insert into @sales values(2002, 'Q1', 20)
insert into @sales values(2001, 'Q2', 15)
insert into @sales values(2002, 'Q2', 25)
insert into @sales values(2001, 'Q3', 50)
insert into @sales values(2002, 'Q3', 20)
insert into @sales values(2001, 'Q4', 90)
insert into @sales values(2001, 'Q4', 80)
insert into @sales values(2002, 'Q4', 35)
select * from @sales
PIVOT (
SUM(Amount)
for Quarter in (Q1, Q2, Q3, Q4)) as p
Yields...
Incorrect syntax near 'SUM'.
View 2 Replies
View Related
Aug 1, 2014
I'm trying using the GROUP BY CUBE aggregation. Currently I have this working as such:
SELECT
ISNULL(CONVERT(VARCHAR,Date), 'Grand Total') Date
,ISNULL([1 Attempt],0) [1 Attempt]
,ISNULL([2 Attempts],0) AS [2 Attempts]
,ISNULL([3 Attempts],0) AS [3 Attempts]
,ISNULL([4 Or More],0) AS [4 Or More]
[Code] .....
Basically this is used to work similar to a Pivot table in excel. My data will look as follows:
Date 1 Attempt2 Attempts3 Attempts4 Or MoreTotal
2012-09-04 239 68 2 8 317
The problem I'm having is the Total column. Although this is summing the line values correctly, the total should be based on the sum not count of attempts i.e. 1 x 239, 2 x 68, 3 x 2, 4 x 8
If I change the FROM select clause to use SUM instead of COUNT
SELECT
CONVERT(DATE,[Date]) Date
,ISNULL(AttemptsFlag,'Total') as Attempt
,SUM(NoOfTimes) AS Totals
FROM
XXXXX
GROUP BY
CUBE([Date],AttemptsFlag)
It will return the correct Total amount but not the right numbers for the Attempt groupings...
View 1 Replies
View Related
Jun 25, 2007
Hi, all experts here,
I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.
With best regards,
Yours sincerely,
View 11 Replies
View Related
Dec 7, 2005
Hi
I am trying to convert foxpro database table example mytable.dbf in to MSSQL Database table. I have created odbc connection to foxpro database, Then i have created an data set in which i have fill the mytable (stucture and data) by odbc adapter. Now i want to import this Data Set in to the sql database. My table (stucture and data) is in memory and my proble is that how i can write to sql data set or create data table which i can see in MSSQL Database.
Please Suggest
Thanks in Advance.
View 4 Replies
View Related
Sep 17, 2015
I cannot create a measure that returns results for dates that do not exist in the fact table despite the fact that the components included in the measure contain valid results for these same dates.Creature a measure that counts the number of days where the "stock qty" is below the "avg monthly sales qty for the last 12 months" (rolling measure).Here is the DAX code I have tried for the measure (note that filter explicitly refers to the date table (called Calendar) and not the fact table):
StkOutCnt:=CALCULATE (
COUNTROWS ( VALUES ( Calendar[DateKey] ) ),
FILTER (
Calendar,
[Stock qty] < [Avg Monthly Sales Qty L12M@SKU]
)
)
Below you can see the sub measures (circled in red) are giving results for all days in the calendar.Highlighted in yellow are dates for which the StkOutCnt measure is not returning a result. Having investigated these blank dates, I am pretty confident that they are dates for which there are no transactions in the fact table (weekends, public holidays etc...).why I am getting an "inner join" with my fact table dates despite the fact that this is not requested anywhere in the dax code and that the two sub measures are behaving normally?
View 6 Replies
View Related
Feb 12, 2007
Hi,
For the Data Driven Subscription in SSRS we are using the following stored procedure
In Step 3 - Create a data-driven subscription
create procedure spRSGetReportSettings
(
@ReportID as integer
) as
begin
set nocount on
declare @t as table(y int not null primary key)
declare
@cols as nvarchar(max),
@y as int,
@sql as nvarchar(max)
set @cols=stuff(
(select N',' + quotename(y) as [text()]
from (select ParameterName as y from Reportsettings where reportid=1) as Y
order by y
For XML Path('')),1,1,N'');
set @sql=N'select * from
(select reportid,parametername, parametervalue from ReportSettings where reportid= ' + Cast(@ReportID as varchar(5)) +' ) as D
pivot(min(parametervalue) for parametername in(' + @cols +N')) as p'
exec sp_executesql @sql
end
Basically the idea is to maintain a single report parameter setting table for multiple reports.
Structure of the table is as given below
ReportID, ParameterName, ParameterValue.
Using Pivot we can generate the ParameterName/ParameterValue combinations for each report. This stored procedure is working fine in query editors(Management Studio)
But, in SSRS it is giving any results.
In Step 4 - Create a data-driven subscription,
Get the value from the database drop down, I am not getting any database columns.
Please help.
Kumar
View 3 Replies
View Related
May 28, 2015
I have this table:
with actividades_secundarias as (
select a.*, r.Antigo, r.Novo, rn = row_number()
over (PARTITION BY a.nif_antigo, r.novo ORDER BY a.nif_antigo)
from ACT_SECUNDARIAS a inner join
((select
[Code] ....
I want to make a delete statement like this:
select * into #table1 from actvidades_secundarias where rn>1
Delete from act_secundarias where act_secundarias.nif_antigo = #table1.nif_antigo and act_secundarias.cod_cae = #table1.cod_cae
But it seems that I cant delete like this.
View 5 Replies
View Related
Nov 20, 2007
Hi,
These aren't my tables, but lets dumb it down for my sake!
I have 2 tables:
CompanyInfo: IDNumber, EffectiveDate, CompanyName
TransacationInfo: TransactID, CompanyID, TransDate, Amount
There are several records in TransactionInfo for each record in CompanyInfo.
I want to:
SELECT CompanyInfo.IDNumber, CompanyInfo.EffectiveDate, CompanyInfo.CompanyName,
SUM(TransactionInfo.Amount)
How can I do this? Can I?
View 4 Replies
View Related
Aug 15, 2012
I need to analyze the results from Table 1, count how many records there are where one field matches, and then put some of Table 1's information into Table 2 along with the count of the records that match. This is built to interface with another system, so a lot of the information, names, and relationships can't always be changed.
Table 1: tblResourceAllocation
Fields (Description):ID (Unique)LastName (Unique alphanumeric string for each employee, but one employee can have multiple records in this table)Project (Unique name, can have multiple entries in this table)Owner (Unique alphanumeric string for each owner, but one owner can have multiple records in this table)ResourceStartDate (datetime)ResourceEndDate (datetime)ServiceID [I](this is what I want to aggregate)Status_FC [I]
Table 2: tblGRFM
ID (unique)Pm (the tblResourceAllocation.Owner's actual full name, which I'll look up from an EmployeeData table and concatenate the FirstName and LastName)IdClient (from tblResourceAllocation.Project I will look up the Customer from the Projects table, then look up the Customer ID from the Customers table)US_State_Id (look up State from Projects table then ID from tblStateCodes)Project_name (same as tblResourceAllocation.Project)Status (same as tblResourceAllocation.Status_FC)Id_operlst (same as tblResourceAllocation.ServiceID)Start (earliest start date of relevant entries)end (latest end date of relevant entries)Nb_ress (total number of ResourceAllocation entries that match Id_operlst)Cadence (explanation below)
I need to calculate how many of each serviceID's are in table 1, and then store that calculation and the ServiceID (Id_operlst) in table 2I need to separate those calculations by Owner, IdClient, US_State_Id, and Project_Name
I.E. If there are 5 entries in table 1 with ServiceId = X, and Owner A is listed on 2 of them and Owner B is listed on 3, I want it displayed as [Owner A, X, 2] and [Owner B, X, 3] in table 2.
Same goes for the other fields, which obviously adds more complexityWithin those calculations I also need to calculate the Start and end date for table 2. Using the last example, if one entry of Owner A's service X has the early start date and the other entry has the later end date, those dates need to be displayed in the Start and end for table 2 (should I use Rank here?)
I need to create a string of numbers separated by semicolons that shows how many resources are working on a given week after the start date
Example:I have 3 resources on a projectResource 1: 12/31/2012-1/27/2013 (Week 1-4 of our calendar)Resource 2: 1/7/2013-1/27/2013 (Week 2-4)Resource 3: 1/7/2013-1/27/2013 (Week 2-4)tblGRFM.Start will equal 12/31/2012 and tblGRFM.end will equal 1/27/2013Cadence should be equal to "1;3;3;3" to indicate that one resource will be working the project the first week, and three resources will be working the project for the next three weeks.
View 2 Replies
View Related
Apr 14, 2000
Hi anyone can tell me how to create a table from an existing table in MSSQL.
In oracle we issue " create table <tablea> as select * from <tableb>; " ?
Thanks in Advance.
View 3 Replies
View Related
Jun 21, 2007
Hi there,
Is there an optimize table command in mssql which will work the same way as "OPTIMIZE TABLE tablename" of mysql?
I have a php application that should work on both mysql and mssql. To do defragmentation, I am using the above command. Is there an equivalent in mssql?
Cheers,
Celia
View 1 Replies
View Related
Mar 8, 2006
In order to update a MSSQL table, named [Table_1]. The procedure is:
(@id int,@Field0 nvarchar(1000) = NULL,@Field1 nvarchar(1000) = NULL,... ...@Field9 nvarchar(1000) = NULL)Method 1:
UPDATE Table_1SET Field0 = CASE WHEN @Field0 IS NULL THEN Field0 ELSE @Field0 END, Field1 = CASE WHEN @Field1 IS NULL THEN Field1 ELSE @Field1 END, ... ... Field9 = CASE WHEN @Field9 IS NULL THEN Field9 ELSE @Field9 ENDWHERE id = @id
Method 2
IF @Field0 IS NOT NULL UPDATE Table_1 SET Field0 = @Field0 WHERE id = @id ... ... UPDATE Table_1 SET Field9 = @Field9 WHERE id = @id
Method 3:
DECLARE @SQL nvarchar(max)DECLARE @NeedComma bitSET @SQL = 'UPDATE Table_1 SET 'SET @NeedComma = 0IF @Field0 IS NOT NULL BEGIN IF @NeedComma = 1 SET @SQL = @SQL + ', ' SET @SQL = @SQL + 'Field0 = @Field0' SET @NeedComma = 1 END... ...IF @Field9 IS NOT NULL BEGIN IF @NeedComma = 1 SET @SQL = @SQL + ', ' SET @SQL = @SQL + 'Field9 = @Field9' SET @NeedComma = 1 ENDSET @SQL = @SQL + ' WHERE id = @id'EXEC sp_executeSQL @SQL, N'@id int,@Field0 nvarchar(1000), ... ... , @Field9 nvarchar(1000)', @id = @id, @Field0 = @Field0, ... ... , @Field9 = @Field9
Which is the best? Thanks!
View 3 Replies
View Related
Sep 23, 2004
Hello all,
I have recently started working on a project which involves using MSSQL to access a simple database. I have worked with Postgres SQL before, so I have a general idea of what SQL can be used for, but I'm having some difficulties applying that knowledge to MSSQL.
Currently, I would like to do the following (in abstract terms):
declare tmp record
select column1 from tableA into tmp
for each entry from above selection do
insert into tableB values (tmp[column1], 0, 0, 0)
I remember doing something like this fairly easily in postgres. Trying to put that into MSSQL, I have:
CREATE FUNCTION dbo.newDay (@mDate datetime)
RETURNS int
AS
BEGIN
DECLARE @id int
DECLARE item_cursor CURSOR FOR
SELECT id FROM tblKitchenCat
OPEN item_cursor
FETCH NEXT FROM item_cursor INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO tblKitchenList VALUES (@id, 0, 0, 0, 0, 0, @mDate)
FETCH NEXT FROM item_cursor INTO @id
END
CLOSE item_cursor
DEALLOCATE item_cursor
RETURN 0
END
GO
I get a syntax error next to AS... what is it?
Can somebody please help me out here... any articles related to moving to MSSQL from Postgres would also be highly appreciated.
In addition to that, I would like to schedule a particular function to run once a day, say at 2am. Is there a way to do this using MSSQL?
Thanks in advance.
Cheers,
Michael
View 3 Replies
View Related
Jun 8, 2007
Hello all,
I am Using MSSQL and ASP Classic for this project. Let me explain my problem, i have two tables:
Users
ID
Username
ChangeLog
ID
Description
UserID
DateChanged
The Users table will contain one record for each user in the system.
The ChangeLog table can contain any number (including 0) of rows relating to each user, when a change to the users account is made, and entry is put into the ChangeLog table.
The object of my report is to display a list of all users in the system and display the date and description of the last change made to the user.
So far i have written this:
Code:
SELECT Users.ID, Users.Name, ChangeLog.Description, ChangeLog.DateChanged
FROM Users
LEFT OUTER JOIN ChangeLog ON (ChangeLog.UserID = Users.ID)
ORDER BY ChangeLog.DateChanged DESC
However, if a user has got more than one change against it then a row will be returned for each change instead of just showing the latest change. So i then tried this:
Code:
SELECT Users.ID, Users.Name, ChangeLog.Description, ChangeLog.DateChanged
FROM ChangeLog
LEFT OUTER JOIN Users ON (Users.ID = ChangeLog.UserID)
ORDER BY ChangeLog.DateChanged DESC
The problem with this one? It only shows users that have actually had a change made to them, and the report *needs* to display ALL users regardless of wether they have been changed or not.
I'd appretiate any help you can give me at all, thanks in advance!
View 5 Replies
View Related
Jun 23, 2008
Hello,
In MSSQL2000, I am able to transfer files from one db to other.
using addTask -> export data.
In MSSQL2005, I am not able to transfer files from one db to other.
Please advise.
Thanking You.
Regards,
Edward
View 16 Replies
View Related
Sep 28, 2006
I am new to SQL Server.
I have a fox pro database that is part of a vendor's product. I want to link some of the individual tables in the foxpro database in my SQL database.
Can someone point me to a tutorial or example on how to do this?
Thanks!
View 4 Replies
View Related
Apr 26, 2005
(i am not sure which forum to post this too)
Hi, I imported a table onto my host's server, table_Login which has username password, and userID. userID is Primary Key & identity.
When I had my code do insert of new user, I was getting error the Login.userID doesn't allow null values. Which shouldn't have mattered because userID is an identity.
After making a diagram from Enterprise Manager, I noticed that I have two Login tables (although only one shows in Enterprise Manager's "Table" node view. The second table, which had none of the constraints that my table of the same name which is listed under the "Table" node does; appears to be the table which is being written to by my code.
I can't find this table, Login(dbo), through Enterprise Manager, nor am i sure how to access it through Query Analyzer. Needless to say, I don't like this arrangement. and well tech support for this host seems to leave a lot to be desired.
Anyone know what needs to be done so that I can fix it or fight with tech support to convince them that they should?
View 2 Replies
View Related
Dec 7, 2005
Just like "describe select(*) from table_name" in DB2?
Thanks!
View 6 Replies
View Related
May 19, 2006
My company would like to start keeping track of everytime a table, stored procedure, or function is changed.
What is the best way to do this?
View 4 Replies
View Related
