Adding Columns To A Query From Results

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


ADVERTISEMENT

Transact SQL :: Adding Results Of Query To Another Query Via Dynamically Added Columns

Jul 30, 2015

For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?

I want my output to be

CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc

Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.

I want to do it using SELECT. Is it possible?

View 13 Replies View Related

Adding Query Results To A Tbale

Jan 13, 2004

hello everybody,

I have a small problem and was hipeing someone could help. Want to run a query, where I sellect results from two tables. Once this is done I want to takes those results and update a table that is already created with those results. How would I do this? Thw eonly way I know how to do this is by writing this:

select CompanyName, ContactName, Address, City, Region, ProductName, UnitPrice INTO TABLE2
From Suppliers, Products

But the problem with this is that I can only input the query results into a new table that I would name above. I can't insert it into a table that has already been created.

This is the erroe that I receive:
There is already an object named 'TABLE2' in the database.

Please Help!

thanks

View 8 Replies View Related

SQL Server 2012 :: Adding 2 COUNT Statements Results In Heavy Query

Jan 28, 2014

These separate COUNT queries are very fast:

SELECT COUNT(id) as viewcount from location_views WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357
SELECT COUNT(id)*2 as clickcount FROM extlinks WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357

But I want to add the COUNT statements, so this is what I did:

select COUNT(vws.id)+COUNT(lnks.id)*2 AS totalcount
FROM location_views vws,extlinks lnks
WHERE (vws.createdate>DATEADD(dd,-30,getdate()) AND vws.objectid=357)
OR
(lnks.createdate>DATEADD(dd,-30,getdate()) AND lnks.objectid=357)

Turns out the query becomes immensely slow. There must be something I'm doing wrong here which results in such bad performance, but what is it?

View 7 Replies View Related

Adding Columns Of Data In An Aggregate Query

Mar 14, 2001

Ok so I need to write a query that finds the lowest grade out of a group of students (by a class number), and identifies it by a student id #.

Here's what I'm trying:

select min(gr.grade), gr.stu_id
from dbo.class cl, dbo.grade gr
where (cl.class = 2) and (abs(cl.stu_id) = abs(gr.stu_id))
group by gr.stu_id

but unfortunetly it returns the lowest grade for each student in class 2, and not the single lowest grade in class 2 and the stu_id (student ID).

I'm new to SQL and could really use the help.

Thanks

View 1 Replies View Related

Query Names Of Stored Procedure Results Columns?

Mar 2, 2012

I am imagining something you might pass the names of 2 stored procs (an old version and new one), and a query to produce valid parameters. It would then fire off each proc for a set number of executions, while storing off the results in temp tables, and at the end it would do a data compare, and store off performance data from dynamic management views.

Now I know how to get the parameters for a stored procedure out of the catalogue views, but is SQL Server aware at all of the schema of the results of stored procedures that return result sets, becuase I was thinking of doing something like...

INSERT INTO #datacompare(col1,col2)
EXEC mystoredprocedure

... but I can not seem to figure out how to dynamically gather the schema of the result set.

View 1 Replies View Related

Select Query Results In Multiple Columns Based On Type From Another Table

Apr 6, 2008

Using SQL Server 2005 Express:
I'd like to know how to do a SELECT Query using the following tables:

Miles Table:
Date/Car/Miles/MilesTypeID
===============
(some date)/Ford/20/1
(some date)Ford/20/2
(some date)Chevy/30/1
(some date)Toyota/50/3
(some date)Ford/30/3


Miles Type Table
MilesTypeID/MilesType
=================
1/City
2/Highway
3/Off-Road

I'd like the results to be like this:

Date/Car/City Miles/Highway Miles/Off-Road Miles
=====================================
(date)-Ford-20-0-0
(date)-Chevy-0-20-0
(date)-Ford-20-0-0
(date)-Toyota-0-0-50
(date)-Ford-0-0-30

Anyone? Thanks in advance!

View 3 Replies View Related

Reporting Services :: Adding All Columns To Table Without Adding One By One

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

Adding The Results Of 2 Queries

Jun 21, 2007

Hi,
I have to queries that return tables with the same names. How do i add these 2 so it returns one table?
Thanks for your help.
Mike

View 7 Replies View Related

Adding New Lines Into Results In The

May 15, 2006

I used to be able to enter new lines into the result pane cell for text(and varchar) data in Enterprise Manager, but now that I am using SQL2005 Management Studio, this feature is gone.Is there any way to do this?Also, copying to/from excel chops off part of the text in a cell and isvery infuriating.Any help would be appreciated.Dan

View 2 Replies View Related

Is There A Way To Hold The Results Of A Select Query Then Operate On The Results And Changes Will Be Reflected On The Actual Data?

Apr 1, 2007

hi,  like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right?  so, is there something that i can use to hold those records so that i can do the delete and update just on those records  and don't need to query twice? or is there a way to do that in one go ?thanks in advance! 

View 1 Replies View Related

Need To Display Results Of A Query, Then Use A Drop Down List To Filter The Results.

Feb 12, 2008

Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.

View 1 Replies View Related

Adding Columns To A Matrix Report That Don't Belong To The Matrix Columns Groups

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

SQL Server 2012 :: Adding RowID To Existing Table - Inconsistent Results

May 6, 2015

I am getting inconsistent results when BULK INSERTING data from a tab-delimited text file. As part of my testing, I run the same code on the same file again and again, and I get different results every time! I get this on SQL 2005 and SQL 2012 R2.

We have an application that imports data from a spreadsheet. The sheet contains section headers with account numbers and detail rows with transactions by date:

AAAA.1234 /* (account number)*/
1/1/2015 $150 First Transaction
1/3/2015 $24.233 Second Transaction
BBBB.5678
1/1/2015 $350 Third Transaction
1/3/2015 $24.233 Fourth Transaction

My Import program saves this spreadsheet at tab-delimited text, then I use BULK INSERT to bring the data into a generic table full of varchar(255) fields. There are about 90,000 rows in each day's data; after the BULK INSERT about half of them are removed for various reasons.

Next I add a RowID column to the table with the IDENTITY (1,1) property. This gives my raw data unique row numbers.

I then run a routine that converts and copies those records into another holding table that's a copy of the final destination table. That routine parses though the data, assigning the account number in the section header to each detail row. It ends up looking like this:

AAAA.1234 1/1/2015 $150 First Purchase
AAAA.1234 1/3/2015 $24.233 Second Purchase
BBBB.5678 1/1/2015 $350 Third Purchase
BBBB.5678 1/3/2015 $24.233 Fourth Purchase

My technique: I use a cursor to get the starting RowID for each Account Number: I then use the upper and lower RowIDs to do an INSERT into the final table. The query looks like this:

SELECT RowID, SUBSTRING(RowHeader, 6,4) + '.UBC1' AS AccountNumber
FROM GenericTable
WHERE RowHeader LIKE '____.____%'

Results look like this:

But every time I run the routine, I get different numbers!

Needless to say, my results are not accurate. I get inconsistent results EVERY TIME. Here is my code, with table, field and account names changed for business confidentiality.

TRUNCATE TABLE GenericImportTable;
ALTER TABLE GenericImportTable DROP COLUMN RowID;
BULK INSERT GenericImportTable FROM 'SERVERGeneralAppnameDataFile.2015.05.04.tab.txt'
WITH (FIELDTERMINATOR = ' ', ROWTERMINATOR = '', FIRSTROW = 6)
ALTER TABLE GenericImportTable ADD RowID int IDENTITY(1,1) NOT NULL
SELECT RowID, SUBSTRING(RowHeader, 6,4) + '.UBC1' AS AccountNumber
FROM GenericImportTable
WHERE RowHeader LIKE '____.____%'

View 3 Replies View Related

Adding Columns

Jan 11, 2004

What is the SQL syntax for adding a column to an already created DB table?

View 4 Replies View Related

Adding Columns

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

Adding Columns

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

Adding Columns

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

Adding Columns

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

Help Adding Columns

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

Adding Columns In A Certain Order

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

Adding Values To Columns

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

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

Adding Columns To A Table

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

Adding Two Values From Two Columns

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

Adding DEFAULT Columns

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

Adding Different Columns From Different Table

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

Adding Data To Columns

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

Adding Columns Dynamically

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

Adding Columns For A Dollar Total

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

An Interesting Issue With Adding Columns.

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

Adding Columns With Different Time Unit

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

Adding Columns To An Existing Table

Dec 18, 2007

how do i add columns to an existing table?

View 1 Replies View Related







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