T-SQL (SS2K8) :: Adding Columns On The Fly
Apr 21, 2014
I have a report that looks like the following
NameCity Client NoAccount No Balance
SmithSydney 1234561258792 3.95
JonesMelbourne 2589641000657 9.54
BrownPerth 9876541000879 5.46
BrownPerth 9876541000880 7.51
WhiteSydney 6548521007562 10.65
HOWEVER I need it to look like the following:
NameCity Client NoAccount No BalanceAccount No Balance
SmithSydney 1234561258792 3.95
JonesMelbourne 2589641000657 9.54
BrownPerth 9876541000879 5.461000880 7.51
WhiteSydney 6548521007562 10.65
The requirement is that if the Client No is the same that the Account No and Balance appear on the same row but just additional columns. There is no restriction on how many extra columns there would be.
Once the query is working it needs to go into Visual Studio so that the report can be set up as a subscription.
View 1 Replies
ADVERTISEMENT
Sep 3, 2015
Is there any way or option to get the all columns of dataset added to table when we add a table in data region. It will take lot of time to add one by one and also there are chances to add one column ore than once.
View 7 Replies
View Related
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
Jun 11, 2015
Basically, I'm given a daily schedule on two separate rows for shift 1 and shift 2 for the same employee, I'm trying to align both shifts in one row as shown below in 'My desired results' section.
Sample Data:
;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS
(
SELECT 5060,'04/30/2015','05:30', '08:30'
UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30'
UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30'
UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'
[Code] ....
The results from the above are as follows:
columnAcolumnB SampleTitle1 SampleTitle2 SampleTitle3 SampleTitle4
506004/30/201505:30 NULL NULL NULL
506004/30/201513:30 15:30 NULL NULL
506005/02/201505:30 NULL NULL NULL
506005/02/201513:30 15:30 NULL NULL
My desired results with desired headers are as follows:
PERSONSTARTDATE STARTIME1 ENDTIME1 STARTTIME2 ENDTIME2
506004/30/2015 05:30 08:30 13:30 15:30
506005/02/2015 05:30 08:30 13:30 15:30
View 3 Replies
View Related
Mar 20, 2014
I have a query that brings in a result from a table that I have to display as a negative, so I use ABS (tbname.fieldname) *-1
This works fine. (Learnt it here from a previous post.)
I have another query that brings in a result from the same table but as a postive. So that works fine too.
Now, I need to sum them together in a new query. If the total value is a positive, fine. If the total value is a negative, it displays as a positive.
My question. Is it actually possible to achieve what I'm trying to do?
Output example below - narrow result for testing.
idpt2 Value2SumQty
N1141307-80970 3.80 2
N1141S2G00009D070 26.16 2
The 3.80 is correct, it is a positive.
The 26.16 is incorrect as it is the output from sum 235.44 -261.6 so I need it to display as -26.6
View 9 Replies
View Related
Oct 15, 2014
This statement adds two additional months to which is fine :
DATENAME(MM,dd.date)+ ' ' + DATENAME(D,dd.date) + ', ' + DATENAME(YY,dd.date)
but if my month is November and two months is added, the year does not change, it stays the same. how do I make the year change when two months are added toward the end of the year.
View 7 Replies
View Related
Mar 27, 2015
I have a datetime field that is just the date and zero for the time element. I also have a varchar field that is of the format 09:25:30
is there an easy way to add these together?
I don't mind if they get converted to integer as it will be used for comparison.
View 9 Replies
View Related
Jan 2, 2007
Can we do this?
Adding more columns in a matrix report that don€™t
belong to the columns drilldown dimensions€¦
That is, for example, having the following report:
Product Family
Product
Country City Number of units sold
Then I
would add some ratios, that is, Units Sold/Months (sold per month) and other that
is the average for Product Family (Units Sold/Number of Product Family), for putting an example€¦ some
columns should be precalculated prior to the report so do not get into it, the
real problem I don€™t see how to solve is adding one or two columns for showing
these calculated column that doesn€™t depend on the column groups but they do
for the rows groups€¦
Any guidance
on that?
The only
way I am seeing by now is to set it as two different reports, and that is not
what my client wants€¦
Many
thanks,
Jose
View 4 Replies
View Related
Jan 11, 2004
What is the SQL syntax for adding a column to an already created DB table?
View 4 Replies
View Related
Jun 10, 2008
I have sales for a store per day, I need to add that sales for the day so say on the 4th of the month it has total sales of that day plus all previous days.
View 2 Replies
View Related
Jul 31, 2006
Hi
I am quite new to the delights of SQL and wondered if some professional could suggest the best approach to tackling the following problem:
If I had the following table:
Runner Result1 Result2 Result3 Result4
Dave 4 4 3 2
Phil 2 3 3 3
Derek 2 4 6 7
And I wanted to add and display a column that would display “best 3 total results�, such as:
Runner Result1 Result2 Result3 Result4 Best3
Dave 4 4 3 2 11
Phil 2 3 3 3 9
Derek 2 4 6 7 17
Any help would be really appreciated.
I would be using asp.net vb.net and sqlserver2003
View 14 Replies
View Related
Mar 5, 2007
Is there a way to add two columns (with numbers) that have already been aliased? I summed the two columns separately, and also want to display a third colum that show the sum of the two already summed columns. Any help is appreciated. thanks.
View 1 Replies
View Related
Jul 12, 2007
Hi, hope someone can help me with this, I have something like this:
sum(per1) as period1, sum(per2) as period2, sum(per3) as period3
And I want to add all the periodX toghether, how can I do it?
On my real SQL query the SUM statement is more complex, with some cases and such.
Also can I divide this result by a number?
View 11 Replies
View Related
Jan 25, 2008
I have this code and it should bring back code for the month i put in in the set statement. When i put in say may i need to add up numbers for all months prior in the total projections call em and im lost
Declare @Division as Varchar(15)
Declare @Date as Varchar(7)
Set @Division = 'Del Sol'
Set @Date = '05/2008'
Select [Month], Sum(Projection) as ProjectionTotal
From Reporting.dbo.RetailSalesComparison_ProjectionsView PV
Inner Join DelsolNet2.dbo.StoreGroup SG on PV.StoreID = OrderStoreID
Where @Division = Case DivisionID When 10 then 'Del Sol'
When 11 then 'Cariloha'
When 12 then 'Sol Kids' else 'Notta' End
and Month([Month]) = Left(@Date,2) and Year([Month]) = Right(@Date,4)
Group By [Month]
View 2 Replies
View Related
Sep 4, 2014
The FirstName and LastName values being passed in are in cyrillic text.
How do I add the N prefix in the update statement in order to store the text correctly.
I've tried FirstName = N@Firstname
or FirstName = '''N''' + @FirstName + '''
[dbo].[sp_UpdateDealerPeopleInfo](
@PersonId char(9),
@OriginalSID char(9),
@FirstName Varchar(50),
@LastName Varchar(50),
[code].....
View 8 Replies
View Related
Nov 20, 2000
I am familiar with the ALTER TABLE syntax that can be used to add columns to an existing table, but the columns are appended to the end of the table. Enterprise Manager allows you to insert a column in the desired place in the table, but I suspect that behind the scenes it creates a temp table with the new structure, drops the old version of the table and renames the temp table to the orginal table name (I could be wrong on this).
Is there a way to insert a new column in a specific place in a table using SQL rather than EM?
View 1 Replies
View Related
Jan 17, 2012
I have a table with 6 columns. which we can call a, b, c, d, e, f. What I want to achieve is to put data in column d and e and then split this result in column f.
The data I want to put in column d and e is already exported and executed from a table called exp_data, which is from a period of november.
So this is what i have so far but is not working:
update split_table set d =
select amount from exp_data
where period = '1111'
and exp_data.account = split_table.b
and exp_data.company = split_table.a
The error I get is incorrect syntax near select. Fixed the issue by adding a parenthesis before the select until the end...
View 6 Replies
View Related
Dec 12, 2006
I have a LIVE SQL 2000 database. I am trying to add some new columns to a table. My question is will I need to take the database off-line to perform this operation? I have replicated it to a sample table if I can't.
KL Hutch
View 1 Replies
View Related
Sep 27, 2007
Hi!
I have one table with thousands of records. Now, I have to add one more column to that table. And this new column will contain concatination of two columns in that same table. Is there any command to do this?
My table has two columns as 'file', 'extn', now i want to add 'fullname' column to that table, and it will contain the concatinated value of 'file' and 'extn'. If the 'file' field has one record as "myfile", 'extn' has one record as ".doc", I want to insert "myfile.doc" in the new column 'fullname'. Like this my table has thousands of records. How can i do this? Please help me! Thanks in advance!
View 9 Replies
View Related
Jul 23, 2005
HiI have a table that currently has 466 columns and about 700,000records. Adding a new DEFAULT column to this table takes a long time.It it a lot faster to recreate the table with the new columns and thencopy all of the data across.As far as I am aware when you add a DEFAULT column the followinghappens:a) The column is added with a NULL propertyb) Each row is updated to be set to the DEFAULT valuec) The column is changed to NOT NULL.However, adding the column as NOT NULL with the DEFAULT seems to take alot longer than if I do steps a) - c) separately.When I say a long time, adding just a single DEFAULT column takesaround 6 hours. Surely it should not take this long?There is a trigger on this table but disabling this does not seem tomake much difference.Can anybody give me any advice on the use of DEFAULT columns please?When should they be used, benefits, disadvantages, alternatives etc.Also should it really take as long as it is taking or is there aproblem with my setup?If I am honest I can't see why DEFAULT columns should be used as thevalues could always be inserted explicitly via the applicationThanks in Advance.Paul
View 5 Replies
View Related
Sep 22, 2007
I have three tables.
Member(name, address, ID)
Loan(ID, startdate, amount)
Deposite(ID, startdate, amount)
I wanna create a report which look like this.
ID MembersName startdate address etc
Member can be either borrower or a depositor.
I'm thinking of using inner join. Can anyone help me to write the query?
Thanks
View 3 Replies
View Related
May 11, 2006
Hi
I'm am using SQL Server 2005 Express on my home PC.
To practice some of the SQL skills I learned on a course I was recently on, I am trying to build a mini database which I can run some queries from.
I have created some tables and added columns to these tables. However, I cannot find how to add data to these columns.
I'm sure it's pretty simple but I just can't find it! Can anyone help??
Cheers!
View 4 Replies
View Related
Sep 3, 2006
Dear All,
I have a requirement, in which i have to add columns dynamically depending the records of a master table in sql server 2000.
Pl, do help me
View 2 Replies
View Related
Jun 17, 2008
I was wondering what is the best way to have a amount paid total from amounts entered to a specific id or column? I am using SQL 2005. I need to be able to tally the total amount paid and put that value in another column called amount paid. Any help would be great.
Thanks,
View 9 Replies
View Related
Nov 7, 2006
I've written this procedure that is a meta data manager if you will. It looks at a vocabulary table and will create or alter tables to be in line with the meta data. It works great and has for a couple of years now. However, I just rewrote it in an effort to make it more compact, efficient, etc. Now, our system uses at least three databases. One contains shared data, the next holds only system data for the app like individual user settings, etc (atslogin is the name) then would be an actual database for client data or accounts as we call them. Clients could have one or 100 of those. Please, no comments about moving everything in to one DB as that is not always possible to to hierarchy of multiple business models.
Now, on to the issue. This procedure used to exists in the local database (one or many) now I keep it in atslogin so in effect, it looks "down" on all other databases and does it's thing. It adds columns, expands them, creates tables, views, keys blah blah blah.
Here is the issue. When it creates a new column the ordinal position is out of whack. I've got a table where I keep dropping the last column, run my procedure to add it and find that the ordinal position has increased by one each time. Add it and the value is 48 for example. Drop it and add it again and it is now 49 and all the while there is a gap between say 47 and 49. This is being written to syscolumns and the view INFORMATION_SCHEMA.[columns].
This is a big deal because if I find columns are out of order I wont attempt to alter the table. Trouble is the columns are in the proper order, I just can buy what the system is saying. Anyone ever seen this? Even if I add a column as the db owner I'm seeing this. This is SQL 2k. Below is the exact version
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
View 14 Replies
View Related
Apr 14, 2014
I have a table called approval_levels in which I have 2 columns:
|LEVEL_ID |NAME |
|1 |app_level_1 |
|2 |app_level_2 |
|3 |app_level_3 |
I have a second table called requests, in which I have 3 columns:
|REQUEST_ID |PRODUCT_NAME |MANUFACTURER |
|1 |wd-40 |Acme |
|2 |Windex |Acme |
|3 |Propane |Acme |
I have a third table which links the two called request_approvals which has 2 columns: REQUEST_ID, LEVEL_ID. When a request is approved for a specific level, I insert a value into this table. So, for example, lets say request 1 has been approved for all 3 levels, request 2 has been approved for only level 1, and request 3 has been approved for level 3, the table would show something like this.
|REQUEST_ID |LEVEL_ID |
|1 |1 |
|1 |2 |
|1 |3 |
|2 |1 |
|3 |3 |
Ok, so here the challenge: I need to show in a report all the requests, create a columns for each level and show whether or not that request is approved for that level. The end result has to be something like this:
|REQUEST_ID |PRODUCT_NAME |MANUFACTURER |app_level_1 |app_level_2 |app_level_3 |
|1 |wd-40 |Acme |X |X |X |
|2 |Windex |Acme |X | | |
|3 |Propane |Acme | | |X |
Keep in mind that if another value is added to the approval_levels table (ie app_level_4), I need to add another column to the table call app_level_4 dynamically.Now, how in the world do I do something like this?
View 1 Replies
View Related
Oct 29, 2014
I have three different columns, hour(s), min(s), sec(s)
I can add it up, but will like to convert it into. hrs, mins and sec.
this is how, i am adding it up into seconds.
SELECT ((TotalTimeSpentHrs*60*60)+(TotalTimeSpentMin*60)+(TotalTimeSpentSec))AS totaltime
FROM EST1
How can I convert the total seconds, so that i can input the result in a new column.
View 1 Replies
View Related
Dec 18, 2007
how do i add columns to an existing table?
View 1 Replies
View Related
Jul 20, 2005
Hi, I'm trying to add a modified datetime and userid to all 72 tables in mySQL 2000 database. I have the script to do one table, and a cursor, but itwon't run across all tables. Any help would be appreciated. Thanks...DECLARE @tName varchar(40)DECLARE C1 CURSOR FORselect name from sysobjects where type = 'U'OPEN C1FETCH NEXT FROM C1 INTO @tName-- Check @@FETCH_STATUS to see if there are any more rows to fetchWHILE @@FETCH_STATUS = 0BEGIN-- This is executed as long as the previous fetch succeedsBEGIN TRANSACTIONSET QUOTED_IDENTIFIER ONSET TRANSACTION ISOLATION LEVEL SERIALIZABLESET ARITHABORT ONSET NUMERIC_ROUNDABORT OFFSET CONCAT_NULL_YIELDS_NULL ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONCOMMITBEGIN TRANSACTIONALTER TABLE @tName ADDModifiedDT datetime NULL,ModifiedUserID int NULLGOCOMMITFETCH NEXT FROM C1ENDCLOSE C1DEALLOCATE C1GO
View 3 Replies
View Related
Oct 18, 2007
Dear Friends,
what is best and quicker way to add 500 columns to existing table having 145 columns already.
Is there any way to avoid manual work of adding columns one by one in design mode or using script.
I have a TXT file (comma delimited) that contains all those columns names as a first row,but I am not sure if i can use DTS package to create table design having such sourcre TXT file.
Any advice?
Thanks,
View 1 Replies
View Related
Mar 6, 2008
Hello Friends,
I have created a report using SSRS and in that report I am using group rows in one of the matrix. When I tried to display the subtotal of that group row by using the SSRS in-built feature the subtotal column is coming at the last of the matrix columns but the subtotal its showing is wrong . Its just giving the value of the first columns value.
Can anyone help me on this issue.
Thanks & Regards
Shivanandan Gupta
View 6 Replies
View Related
Oct 17, 2014
I am currently working on extracting data from a text file and loading into sql server.I have a table with 1 column and the data in the column is in this format:
TableName1
A
1
10
TableName2
D
2
20
I want to convert this to 2 columns in this format:
ColumnA ColumnB
TableName1 A
TableName1 1
TableName1 10
TableName2 D
TableName2 2
TableName2 20
View 1 Replies
View Related
Dec 16, 2014
I've the table structure below. Example table of my original
create table fms
(
fs_locn char(100),
fs_account_no varchar(200),
fs_cost_center_no varchar(100),
fs_tran_type char(50),
fs_post_amt float,
fs_tran_date datetime
[Code] ....
Expecting Output :
Account 200Prod 201PROD ProdcutionCost
E002-SW100-2100 2500 1000 3500
How to do that?
I tried like
select
k.fs_acoounts,
k.200Prod,
(
my query
)k
its showing error '200prod'
View 1 Replies
View Related
