Transact SQL :: How To Return Content Of Table As Columns In Query

Oct 1, 2015

I have

Customer table, tblCust: ID; Name

1 Peter2 Mary
Product table, tblProduct: ID; Name

1 Banana2 Orange3 Apple
Order tblOrder, tblOrder: CustID; ProductID; Amount

1 ;2 ;$20 – means Peter ordered $20 oranges

How do I write the SQL query so that the values in tblProduct become column, currently I have 20 items in that table. So, it will return something like this according to the information that I provide above?

Name Banana Orange Apple 

Peter 0 20 0

View 4 Replies


ADVERTISEMENT

Transact SQL :: Function To Return Comma Delimited Values From Table Columns

May 12, 2015

I've to write a function to return a comma delimited values from a table columns

If a table has Tab1 ( Col1,Col2,Col3).

E.g. as below ( the columnName content I want to use as columns for my pivot table

CREATE FUNCTION [RPT].[GetListOfCol]
(
@vCat NVARCHAR(4000)
)
RETURNS @PList
AS
BEGIN
SELECT @PList += N', [' + [ColumnName] +']'
FROM [ETL].[TableDef]
WHERE [IsActive] = 1
AND [Category] = @vCat
RETURN;
END;

I want out put to be as below, I am getting this output from the select query independently by declaring @Plist variable and passing @vcat value, now I want it to be returned from a function when called from a select query output ,Colum1,column2,....

View 13 Replies View Related

Transact SQL :: Use Dynamic Table Names And Get Return Value From The Query

Sep 16, 2015

I don't know why this is so difficult. What I want to do is take a table name as a parameter to build a query and get an integer value from the result of the query. But from all of the research I have been doing, Dynamic SQL is bad in SQL server because of SQL Injections. But my users are not going to be supplying the table names.

Things I have learned:

 - SQL Functions cannot use Exec to execute query strings.
 - SQL Functions can return a concatenated string that could be used by a stored procedure to Exec the query string.

So how can I write a stored procedure that will
 
1. take a parameter
2. Pass the parameter to a function that will return a string
3. Execute that string as SQL
4. Get a return value from that SQL statement
5. Then finally, from a View, how can I pass a parameter to the stored procedure and get the returned value from the stored procedure to be used as a field in the View?

Numbers 3, 4, and 5 are where I am really stuck. I guess I don't know the proper syntax and limitations of SQL Server.

View 14 Replies View Related

Transact SQL :: Check 2 Columns And Return Rows As Long As One Column Is Not Null

Oct 19, 2015

how to do a check for 2 columns. As long as there is data for at least one of the columns I want to return rows.

Example Data

create table test
(
ID int,
set1 varchar(50),
set2 varchar(50),

[code]....

View 4 Replies View Related

Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?

Apr 29, 2015

I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:

I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.

1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B

If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.

2. My second question: How to i get around this error?

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B,  fnSplitJson2(A.ITEM6,NULL) C

I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.

View 14 Replies View Related

Transact SQL :: Need A Query To Return From LoanStatus

Oct 9, 2015

I have a table which contains a 2 columns "LoanID" and "LoanStatus" and the values in "LoanStatus" are either 0 or 1 in it. If I run a select statement; I get:

LoanID    LoanStatus
1                  0
2                  1
3                  1
4                  1
5                  0

I need a query to return the following from the LoanStatus (distinct Values):

1                 NA
0                 Current
-1                 Late

View 8 Replies View Related

Transact SQL :: How To Return Number Of Records In Query As If TOP Was Not Used

Jul 21, 2015

What I would like to do is to have a TSQL Select return the number of records in the Result as if TOP (n) had not been used. Example:I have a table called Orders containing more than 1.000 records with OrderDate = '2015/07/21' and my client application has a threshold for returning records at 100  and therefore the TSQL would look like

SELECT TOP (100) * FROM Orders Where OrderDate = '2015/07/21'  ORDER by OrderTime Desc

Now I would like to "tell" the client that only 100 of 1.000 records are shown in the client application grid. Is there a way to return a value indicating that if TOP (100) had not been used the resultset would have been 1.000. I know I could create the same TSQL using COUNT() (SELECT COUNT(*) FROM Orders Where OrderDate = '2015/07/21'  ORDER by OrderTime Desc) and return that in a variable in the SELECT statement or even creating the COUNT() as a subquery and return it as a column, but I would like to avoid running multiple TSQL's. Since SQL Server already needs to select the entire recordset and sort it (ORDER BY) and return only the first 100 the total number of records in the initial snapshot must somehow be available.

View 6 Replies View Related

Transact SQL :: Query To Return As Part Of The Fields

Oct 16, 2015

I am working on a query that is quite complex. I need the query to return as part of the fields a field that will contain the total percentage of tickets in a version.The query is below

select cat.name as name,count(distinct bug.id) as numberOfBugs,cast(count(bug.id) * 1000.0 / sum(count(bug.id) * 10.0) over() as decimal(10,2))/100 AS qnt_pct, vers.version, dateadd(s,vers.date_order,'1/1/1970') as "Release_Date"
from mantis_bug_table bug
INNER JOIN mantis_category_table cat on cat.id = bug.category_id
LEFT OUTER JOIN mantis_project_version_table vers on vers.project_id = vers.project_id and vers.version = bug.version

[code]....

View 12 Replies View Related

Query To Return Entire Row Based Upon Two Columns?

Aug 12, 2013

I have a database that has a table that contains basic demographics

•First_name
•Last_name
•Address
•City
•State
•Zip phone
•email
•dob
•etc
•etc

Contained in this table are a lot of duplicate names, and even different addresses. I would like to return an entire row of UNIQUE information based upon “first_name + last name”. Now I know just enough of sql to pull out unique first + last names, but not the entire row based upon this unique information.

View 5 Replies View Related

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

Transact SQL :: Return A Query Based On Dateadd Function

Aug 3, 2015

I'm trying to return a query based on the dateadd function.  I have a column in the database called date_added which is am successfully using the the DATEADD function above as date1.  The Var1 variable I need to populate from the database too from a column called life_span which is an int data type.  The error I get is An expression of non-boolean type specified in context where a condition is expected near select

My query is as follows: select guid, dateadd(day,life_span,date_added) as datepayday. From User_table

View 5 Replies View Related

Transact SQL :: Return Recordset From Dynamic Table

Sep 25, 2015

I tried to create a dynamic table, fill in it and return it as recordset. The codes as this:

Declare @tbl Table(id int, name varchar(100), age int) 
Insert Into @tbl(id, name, age)
Values(1, 'James, Lee', 28),
   (2, 'Mike, Richard', 32),
   (3, 'Leon Wong', 29)
Select * From @tbl Order By age

It works well in "SQL Query Ananizer". But return no records in ASP page.

View 5 Replies View Related

Transact SQL :: Linked Server Return Message Query Timeout

Jul 22, 2015

Error below was returned from an agent job:

OLE DB provider "SQLNCLI11" for linked server returned message "Query timeout expired". [SQLSTATE 01000]

A linked Server was set up against a remote database to backup a database and it runs perfectly well when executed directly against the remote server but returns the above error when set up through Sql Server Agent. Permissions are good as the step returns success. I reset the query timeout property to zero but error persist. What else should I be looking at to make this work?

View 3 Replies View Related

Transact SQL :: Query To Return Greater Than Zero Values To Sort Up And Zeros Go Down

Sep 17, 2015

I am using a table to store different size numbers for example:

Look at the picture below:

But I want this type of output look at the picture below:

How to sort the query to return greater than zero values to sort up and zeros go down. 

I am using sql server 2008.

View 14 Replies View Related

Transact SQL :: Query To Return Free Space From Mounted Drive

Nov 25, 2015

Is it possible to find out available free space from a mounted / network drive using tsql query? I am using sql server 2008 R2.

View 5 Replies View Related

Return Number Of Columns - Pivot Query Into Report Builder

Oct 31, 2014

I have a query which returns number of columns using pivot ( rows into columns -- dynamic sql pivot columns). Since it is dynamic pivot, how can I bind this returned values into report builder matrix reports.

Look at this example :

First time query returns
StudentId | Col1 | Col2 | Col3

Second time query returns
StudentId | Col1 | Col2 | Col3 | Col4 | Col5 | Col6 ...

How to bind this query into report builder 3.0 reports?

View 13 Replies View Related

Transact SQL :: Return Most Recent Entry On Joined Table?

Sep 29, 2015

Empid 1 has 2 entries for the date 09/01/2015 and my left join returns both of those entries.  What do I need to alter to make it so that only the most recent entry is returned not both entries?

Create Table #one
(
empid varchar(100)
,empbadgeid varchar(100)
,emplunchtime decimal(18,4)
,empbreaktime decimal(18,4)
,empworktime decimal(18,4)

[code]....

View 5 Replies View Related

Transact SQL :: Profile Variable To Return From Queue Table?

Jun 18, 2015

Trying to create a report... Report should show * documents on hold then depending on the "on-hold type" look in the corresponding table and SELECT a few fields. Here is what I have. Where do I SET the @profile variable to return the profile from my queue table?

DECLARe
@profilevarchar(256)
SELECT
q.[profile],q.on_hold,q.on_hold_message,q.dbc_state 
FROM
QueueASq

[code]...

View 5 Replies View Related

SQL Server 2008 :: Return A Single Table With Three Columns

Jan 26, 2015

I have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.

I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:

DatabaseName DatabaseVersion DateChangedOn
OK5_AAGLASS 5.10.1.2 2015/01/12
OK5_SHOPRITE 5.9.1.6 2015/01/10
OK5_SALDANHA 5.10.1.2 2014/12/23

The results should be ordered by DateChangedOn.

View 6 Replies View Related

ListChildren Method Does Not Return All Columns From Catalog Table

Dec 5, 2007

I am trying to use the ListChildren method to query the SSRS Catalog. I am using this as a dataset in a report. I'd like to see more columns, in particular the Description column, included in the dataset. Is there some way to tell ListChildren what columns should be included or is there some other method to obtain this information.
ListChildren returns ID, Name, Path, Type, CreationDate, ModifiedDate, CreatedBy, ModifiedBy, xmlns.

I'd prefer not to query the Catalog table directly.

Thanks
Tim

View 1 Replies View Related

T-SQL (SS2K8) :: Multiple Databases - Return A Single Table With Three Columns

Jan 13, 2015

I have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.

I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:

DatabaseName DatabaseVersion DateChangedOn
OK5_AAGLASS 5.10.1.2 2015/01/12
OK5_SHOPRITE 5.9.1.6 2015/01/10
OK5_SALDANHA 5.10.1.2 2014/12/23

The results should be ordered by DateChangedOn.

View 4 Replies View Related

Transact SQL :: Update Query Of Two Columns

Oct 23, 2015

I want to update the NewVol1 and NewVol2 in table #Rec2 from NewVol1 and NewVol2 of table #Rec1. It is some tricky because the value is NULL in some records of NewVol1 and NewVol2.

Update query. SAP, Code, Code1, Code2 and SAPdate are keyfield when join both table to update the NewVol1 and NewVol2 in table #Rec2 from NewVol1 and NewVol2 of table #Rec1.

Create table #Rec1 (SAP char(10), Code char(6), Code1 char(6), Code2 char(6), SAPDate datetime, NewVol1 char(20), NewVol2 char(20))
insert into #Rec1 values  ('SATRIP','AAA','AA','A','2014-01-01','838.88484884',NULL)
insert into #Rec1 values ('SATRIP','AAA','BB','B','2014-01-01','839.88484884',NULL)
insert into #Rec1 values ('SATRIP','AAA','CC','C','2014-01-01','848.88484884',NULL)
insert into #Rec1 values ('SATRIP','BBB','AA','A','2014-01-01',NULL,'383.48377373')
insert into #Rec1 values ('SATRIP','BBB','BB','B','2014-01-01',NULL,'385.48377333')
insert into #Rec1 values ('SATRIP','BBB','CC','C','2014-01-01',NULL,'675.48377234')

[code]....

View 4 Replies View Related

Transact SQL :: Query To Make Rows To Columns

May 18, 2015

I have persons who speaks multiple languages and they are in one table, each row is added if he/she speaks multiple languages. Instead I want to add additional columns and load the data.(what I have shown in the desired output)

name language
-------------
ron   english
ron   french
ron   spanish
andy  english
andy  hindi
kate  english

Desired output

name language1 language2 language3 language4 language5 language6
-----------------------------------------------------------------
ron  english   french    spanish
andy english   hindi
Kate english 

View 4 Replies View Related

Transact SQL :: INSERT Array Of Integer Into Table - Wrong Return Value Of Statement In ODBC

Sep 30, 2015

I would like to INSERT an array of integer into a table in MSSQL Server, then count the number of rows in the table with c++ using ODBC. Here you find my code to do this task:

#include <windows.h>
#include <stdlib.h>
#include <stdio.h>
#include<tchar.h>
#include <sql.h>
#include <sqlext.h>
#include<sqltypes.h>

[Code] ....

In my code, I would like to Insert the array and then count the number of rows:

SQLTCHAR Statement[] = _T("INSERT INTO information1(Wert1,Wert2) VALUES(?,?) select count(*) as a from information1 ") ;

Problem : My expectation is, that first 9 rows are inserted into table then comes 9 as result to user (if the table is empty) but this code returns me 1 if first the table is empty. If the table is not empty, it returns 1+number of  existing rows in the table. If I take a look inside the table, the 9 rows are successfully inserted in it. Only the number of rows in the table is wrong.

Hint : If I monitor the database using SQL Profiler. It looks like this:

Why this statement doesn't work correctly?

View 7 Replies View Related

Transact SQL :: Preserve And Return NULL For Non Matching Values From A Table Valued Function

Jun 29, 2015

I have tables and a function as representated by the code below. The names  for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.

When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.

SELECT  A.CID, 
A.ANI,
A.PID,
B.RD
FROM T1 AS A CROSS APPLY GIVERD(A.PID) B

CREATE TABLE [DBO].[RDTEST](
[PID] [INT] NULL,
[RD] [INT] NULL
)

[Code] ....

View 4 Replies View Related

Transact SQL :: Find Max Of 2 Columns In One Table?

Jul 23, 2015

I am having trouble trying to find the max of 2 columns in one table. I've tried using a common table expression and a subquery, but can't seem to get the correct results. I want to get the max from refnum, then the max "number" associated with that max refnum along with the date and decision

Table
IDCustomerRefnumnumberdate decision
16511114/17/2015Approved
16521125/1/2015Declined
16531216/10/2015Approved
16542116/15/2015Tentative

Expected

Customer 1 had a max of refnum of 2 and 1st one on number

IDCustomerRefnumnumberdate decision
16531216/10/2015Approved
16542116/15/2015Tentative

View 21 Replies View Related

Transact SQL :: Table Name Followed By Columns Names In A Single Row?

May 12, 2015

I am able to get a list of columns in a table from the query I have written shown below:

select sc.name ColumnNames,st.name TableName from sys.columns sc inner join sys.tables st on sc.object_id=st.object_id
order by st.name

But I am looking for the resultset with the format below:

TableName   Columns
employee      employeeid,employeename,employeesalary
order             orderid,address,price 

View 2 Replies View Related

Transact SQL :: How To Add Columns To Temporary Table In Loop

Aug 27, 2015

How to add columns to temporary table in loop with sample code.

View 12 Replies View Related

Transact SQL :: How To Get Distinct Count Of All Columns Of A Table

Dec 3, 2015

I have a table called Employee which have 6 columns. This table are having 1000 records. Now I want to know the distinct value count of all these 6 columns as well as normal count. like this:

ColumnName DistinctCount NormalCount
Id 1000 1000
Name 1000 1000
Phone 600 600
PINCode 200 1000
City 400 1000
Gender 2 1000

View 5 Replies View Related

Transact SQL :: How To Convert Row Specific Values Into Columns In Join Query

Aug 18, 2015

I am using stored procedure to load gridview,i want to show row specific values in coloumns , as i an working on daily timetable of college and There are three tables Week_Day,Daily_Timetable & Subject.Daily_Timetable has data which has week_day,class_id,Subject_id,Period_No.

Each day has 6 periods and each period is mapped with subject in daily timetable.From below sql i am getting 6 rows of monday.

But i want to show in a row weekname,period1_subject_id(Period_No=1),period2_subject_id(Period_No=2),period3_subject_id.......upto
period6_subject_id.

Please see my query below:-

SELECT     Week_Day.Week_Day_name, Subject.Subject_Code,  Daily_Timetable.Period_No
FROM         Week_Day LEFT JOIN
                      Daily_Timetable ON Week_Day.Week_Day_Id = Daily_Timetable.Week_Day_Id and Daily_Timetable.Class_Id=6  LEFT JOIN
                      Subject ON Daily_Timetable.Subject_Id = Subject.Subject_Id order by  Week_Day.Week_Day_Id ,Daily_Timetable.Period_No

View 4 Replies View Related

Transact SQL :: Get DataTypes And Other Schema Related Data Of Columns In A Query

Sep 11, 2015

Is it possible to get data-type,default-value, etc. (basically schema information) of columns in a particular query.This is something similar to what we get when we execute the following 

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employees'

But instead of TABLE_NAME i would like to give a query like

SELECT [EmployeeID],[LastName],[FirstName] from [dbo].[Employees] order by [EmployeeID].

View 10 Replies View Related

Transact SQL :: Join Same Table And Insert Values In Different Columns

Aug 7, 2015

I would like to compare values in the same table and get the single record with different values in the multiple columns.For table tab1, ID is my key column. If type1 is active (A) then i need to update X else blank on Code1 column and if type2 is active (A) then i need to update X else blank on code2 column. Both type1 and type2 comes from same table for same ID..Below is the example to understand my scenario clearly....

declare @tab1 table (ID varchar(20), dt date, status varchar(1), type varchar(10))
insert into @tab1 values ('55A', '2015-07-30', 'A', 'type1')
insert into @tab1 values ('55A', '2015-07-30', 'C', 'type2')
insert into @tab1 values ('55B', '2015-07-30', 'C', 'type1')
insert into @tab1 values ('55B', '2015-07-30', 'A', 'type2')

[code]....

View 4 Replies View Related

Transact SQL :: How To Count Number Of Specific Columns Of Table

Jul 11, 2015

I have a table called Employees which has lots of columns but I only want to count some specific columns of this table.

i.e. EmployeeID: 001

week1: 40
week2: 24
week3: 24
week4: 39

This employee (001) has two weeks below 32. How do I use the COUNT statement to calculate that within these four weeks(columns), how many weeks(columns) have the value below 32?

View 3 Replies View Related







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