Transact SQL :: Getting Primary Key Values As A List Of Values

Oct 14, 2015

I am trying to audit data quality based on some defined data quality rules. The rules are stored in tables and processed using stored procedures. I am facing a problem while generating audits. Let's say I am trying to audit data in OrderDetail table. The table design is mentioned below...I inserted some sample data into the table using RedGate data generator.The audit table output I am expecting is as mentioned in the screenshot below

Its the PrimaryKeyAttributeValues column I am facing problems with. I am using STUFF function within a dynamic SQL query to get the primary key's as a list of comma separated values.

View 2 Replies


ADVERTISEMENT

Transact SQL :: List All Available Values Of A Field?

Aug 10, 2015

I have [TableAccount.AccountType] field which can store these values:

Value
C
S
E

How do I list all available values for a field?  Using Distinct can only list all entries in the db, but does not list all values available for the field.

View 4 Replies View Related

Transact SQL :: Swap Values In 2 Rows Based On Primary Key

Jun 12, 2015

I need swapping values in rows of tables based on primary.

I will explain my scenario with the below example.

ID          Name                  Value   
---         --------                -------- 

1            name1                 202
2            name1                 203
3             name2                203
4             name2                202
5             name3                204

In the above example, I want to swap Value 202 and 203 but its not straight forward. I want to swap in a way that, I need to group by Name column and check if 203 comes first in the group then swap else don't.

In the above example, I need to swap the values for name2 but not for name1. Below query is performing a straight forward swap.

UPDATE TblTest
SET Value
=
CASE
WHEN
Value
=202
THEN203
ELSE202
END

WHERE
Valuein(202,203)

View 10 Replies View Related

Transact SQL :: Convert Comma Separated String Values Into Integer Values

Jul 28, 2015

I have a string variable

string str1="1,2,3,4,5";

I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :

declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)

But this query throws following error message:

Conversion failed when converting the varchar value '3,4,6,7' to data type int.

View 4 Replies View Related

Transact SQL :: CTE To Peg Char Values To Int Values In Radio-style Chart

Aug 26, 2015

I developed the following T-SQL query that runs successfully, but I was looking for a more efficient and concise way to do this. Is there a CTE that can replace all of these case statements?  I've updated my query as below. Although this sample query works, it's not working for my real data.  Instead, I get an error.  At the bottom is the error part of my real query.I copied all of the tables from the first query block below.  But when I wrote the bottom query block, it underlined in red the words "answer" and "question."  It says "Invalid column name". 

if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#hard_values')
) DROP TABLE #hard_values;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#dummy_data')
) DROP TABLE #dummy_data;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#temp')

[code]...

View 13 Replies View Related

Reporting Services :: Count Values In A Column Based Upon Distinct Values In Another Column In SharePoint List

Sep 7, 2015

We have SharePoint list which has, say, two columns. Column A and Column B.

Column A can have three values - red, blue & green.

Column B can have four values - pen, marker, pencil & highlighter.

A typical view of list can be:

Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil

We are looking to create a report from SharePoint List using SSRS which has following view:

                    red     blue   green
    pen            2       0      1
    marker       0       1      0
    pencil          1       3      0
    highlighter  1       1      1 

We tried Sum but not able to display in single row.

View 2 Replies View Related

Transact SQL :: Convert Certain Row Values To Next Column Values

Jul 9, 2015

I have a table with 2 columns and my source data looks like this..

PolicyNum   InsCode    

1ABC12          1001        
1ABC12          1002        
1ABC12          1003       
1ABC12          1004       
1ABC12          1005        

[Code] ....

My output should look like this..I need T-sql to get below output.

PolicyNum   InsCode1   InsCode2    

1ABC12             1001       1005       
1ABC12             1002       1006        
1ABC12             1003       1004       
1ABC20             1001       1005        

[Code] ...

Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.

Rule-1: InsCode value 1001 should always mapped to 1005   
            InsCode value 1002 should always mapped to 1006
            InsCode value 1003 should always mapped to 1004 

Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1

In the above sample data..

for PolicyNum - 1ABC20 , group values 1003,1006 are missing
for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing

Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null)
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')        

Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002')       
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')      

[Code] ....

View 14 Replies View Related

SQL 2012 :: List All Different Values That Go With Single CAS ID To Appear As Comma Separate List

Jun 15, 2015

So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function

So the output would look like :

CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]

SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0

GROUP BY
preop.Case_ID

View 8 Replies View Related

Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure

Aug 19, 2015

I have a stored procedure that selects the unique Name of an item from one table. 

SELECT DISTINCT ChainName from Chains

For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.

SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName

Each row of the result set returned by the stored procedure would contain:

ChainName, Array of StoreNames (or comma separated strings or whatever)

How can I code a stored procedure to do this?

View 17 Replies View Related

Transact SQL :: Update One Table Based On Another Table Values For Multiple Values

Apr 26, 2015

I have two tables  A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B. 

View 5 Replies View Related

How To Select From A List Of Values

Jun 21, 2006

Hi all. I need to create a select query in my program that will select from a list of values that are stored in a dataset. Let see this example:
selectcmd = "Select * from mytable where myfile =" ???????
cmd = New SqlCommand(selectCmd, da.SelectCommand.Connection)
 
The values I need to put on ????? are stored in a dataset. For example if the dataset is populated with the following values:
A
B
C
D
E
 
I would like to build a query like that:
selectcmd = "Select * from mytable where myfile = ‘A’ or ‘B’ or ‘C’ or ‘D’ or ‘E’ “
 
How can I do that?
Thanks.jsn
 

View 6 Replies View Related

Dropdown List Values

Apr 28, 2007

Dear Readers of this post,
 
I have a dropdownlist that is populated with an sqldatasource as follows:
 
SELECT [Project_ID], [Title] FROM [Projects] WHERE [Username] = @Username AND Hide ='false'
 
The Datavalue vield of the DDL is populated with the [Title].
 
When the user submits the form [including the value of the of the drop down list] i want to be able to add the Project ID and the Title Values into another database table.
 
any ideas
 
Sat

View 3 Replies View Related

DropDown List Values

Nov 22, 2005

For a controlParameter in the ASP code, how do I retreive the selectedValue of the drop down list?Would this work?
<asp:controlParameter Name="InvoiceNumber" Type="String" ControlID="ddAdSize.Value" />

View 1 Replies View Related

List All ID Values In A Package

May 16, 2006

While experimenting I got this message:



[Flat File Source [212]] Error: The "output column "Column 0" (237)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "Column 0" (237)" specifies failure on error. An error occurred on the specified object of the specified component"



I note under BIDS, Properties tab, that the Flat File is assigned an ID of 212. But is the number (237) and ID or an error value? How can I list all "ID's" used in a package?



TIA,

Barker

View 1 Replies View Related

How Do I Concatenate Different Values For The Same Primary Key Without Using Cursor..

Mar 21, 2002

Can I avoid cursor (use while of if statement instead ) if for example I have two sales people for the same company and I need one row per company?

View 1 Replies View Related

Choosing Values For Primary Keys

Jul 20, 2005

Hello group:I've done alot of reading on this subject somewhat and have found thatmany people have many different opinions on this subject. My questioncenters mainly around using a lookup table to enable users to select apre-defined list of values.I have developed a practice myself of avoiding AutoNumber type datafields for primary keys where the primary key will be related to achild table. Nevertheless, what do most users do with lookup tables?My thoughts are to create a small key value for each value in thelookup table. For example:I might have a Carriers table which shows a list of carriers that Imight ship an order by. One of the entries may be 'Air Freight -Overnight', or 'Air Freight - 2nd Day Air'. I've seen a few exampleswhere the primary key field for each entry like these would beautonumber, or at least, a numeric value. What I like to do is createmy own key, like for 'Air Freight - Overnight', I might use 'AFO' forthe key, and for 'Air Freight - 2nd Day Air', I might use 'AF2'. Anythoughts on this? Mine are that even tho the users may never see thisvalue - I, as the developer will see it and I tend to prefer a keyvalue based on real data that means something other than anauto-incremented number. In referencing the well-known Northwind.mdbdatabase, I noticed their Categories table used a number field value,like 1, 2, 3....etc, but their customers table used values like'ALFKI' to represent their key values.What are some other thoughts out there? I'm working with Accesscurrently, but this project is about to move to SQL Server.James

View 3 Replies View Related

Insert Values In Primary Key Field

May 15, 2006

Hi,

Does anyone know how should I create a table in order that I can insert values(numbers) in the primary key field, using insert statements. I also would like to know if there are any differences between SQL 2k and SQL 2k5.

Thanks in advance for any reply.

View 1 Replies View Related

SQL Statement - Query A List Of Values

Oct 31, 2006

Hello  I have a newbie question.  If I have a table of the form:Table1{id, name} with the valuesid: 9 , name: test1,id: 7 , name: test2,id: 3 , name: test3,id: 15 , name: test4, id: 5 , name: test5,id: 13 , name: test6,.........If I have a list generated from user selection ( LIST{1, 7, 8, 15} ,) will I in a way be able to use this list in a query of the form, thus only having to make one query to the database: SELECT id, nameFROM Table1WHERE Table1.id in LIST Or is the solution to make multiple queries to the database, one for each member of the list, of the form:SELECT id, nameFROM Table1WHERE ID = @IDThanks in advance /dresen 

View 4 Replies View Related

How To ORDER BY Designated List Of Key Values

Apr 20, 2006

I need to be able to order the results of a SELECT query by the order of a specific list of key IDs provided in the WHERE IN statement.

So my query looks like:

SELECT *
FROM TableName
WHERE KeyID IN (3,104,43,22,345)
ORDER BY ????

I need the results returned in the order provided in the IN list (3,104,43,22,345).

Thanks in advance!

pr0

View 5 Replies View Related

List Of Values That Make SQL Blow

Sep 27, 2006

I am allowing users to assign values in a table to field names in a table
for
EAV table transpostion

Does anyone know where I can find a list of ascii valeus or characters that sql server does not like in fieldnames.

IE (ticks, #, :, -,)

I need to allow the user to define fieldnames for values which then I will alter existing tables with that FieldName that they assign.

I know this is a nightmare but it is the contraint I am working under.

View 14 Replies View Related

SELECT Statement From A List Of Values?

Jul 12, 2012

I would like to write a select statement where I specify a list of values in the 'Select' line, and would like the output to have one line for each element.

I tried using Case with no success.

For example:

Select a.id, a.timestamp, ('rowA','rowB') as 'Tag' from tableOne a where a.id = '1'

So the 'where' line would produce one row, however, the overall statement would produce two.

ID TimeStamp Tag
--------------------------------
1 2012-12-12 rowA
1 2012-12-12 rowB

View 4 Replies View Related

Returning A List Of Different Column Values

Sep 7, 2012

My table has a column called Period i want to get a list of different periods example:
Period
1
2
31
1
2
4
12
31
2

then run an sql statement and should return you the following
Period
1
2
4
12
31

View 3 Replies View Related

Can Store A List Of Values In A Variable?

Apr 16, 2014

I'm looking for a way to store a list of values in a variable. The query user will need to input a list of file numbers, and my query will need to perform a couple operations on that same list of values, which is why it seems a variable would be most appropriate.

I can't obtain the list of values from the database as they will have to be entered by the user. I'm imagining storing these in a table variable.... User just copies/pastes the list of values somewhere into the query code and executes as usual.

View 7 Replies View Related

List All Minutes Between Two DATETIME Values

May 22, 2008

Hi, does anyone know a SQL statement that will take two DATETIME values and list all minutes in between them like so:

START VALUE: 5/22/2008 10:00
END VALUE: 5/22/2008 10:10

RESULT:
5/22/2008 10:00
5/22/2008 10:01
5/22/2008 10:02
5/22/2008 10:03
5/22/2008 10:04
5/22/2008 10:05
5/22/2008 10:06
5/22/2008 10:07
5/22/2008 10:08
5/22/2008 10:09
5/22/2008 10:10

Thanks!

View 8 Replies View Related

List Of Rows With Same Column Values

Nov 3, 2014

select pr.birthdate, pr.operationdate from patientrecord pr
Total no.of rows = 24420

select distinct pr.birthdate, pr.operationdate from patientrecord pr
Total no.of rows = 23528

It seems there are some rows with same birthdate and operationdate.

I want to get the list of rows(with all columns) that has got same birthdate and operationdate

View 1 Replies View Related

Join To A List Of Values During Pass-through

Feb 3, 2015

We're using MS Access 2010 as a frontend to an SQL server back-end. From Access, I can run read-only queries and pass-through queries. I'd like to use a local Access table as part of a join to server data. As a non-pass-through query, it's slow; about 5 min to join to 2 other tables.

I could use VBA to turn the local table into part of a pass-through query, with a large in() statement, or several where x='' ors but the local table may have 50000 entries in it. Is there a good or right way to pass this data in the query if I don't have write access to the server?

View 2 Replies View Related

Where Condition Filter List Of Values

Mar 24, 2015

SELECT
c.CustomerId,
c.CustomerName,
co.OrderKey,
co.OrderNumber,
co.CustomerPO,
co.DueDate,

[code]....

sb.CarrierId data is Sec.

Carriers.ExcludeCarriers I have value as 'Sec,QB' list of values.

I am trying to display sb.Carrierid that is not equal to Carriers.excludeCarriers list of values. It fails now and displays sec values in resultset.

View 1 Replies View Related

Pick List Values Into A Table

Jul 20, 2005

Can anybody help on this question?When I select 3 values from the drop down list box, how can I insert into atable?Please help

View 3 Replies View Related

Set A Attribute To Random Values In A Given List

Jul 20, 2005

Hi,I like to have a SQL script which could update a table to set one attributeto a random value picked from a given list. The prototyping code is asbelow:select @value_list = ('John', 'David', 'Mathew', 'Paul')....update EMP set emp_name = @random_namewhere ...where random name is randomly got from the given list. Of course there willbe a cursor so that different row may have different random name but notnecessary unique. Also the attribute and the list could be any valid commonSQL data typesThanks!Ximing

View 1 Replies View Related

Concatenate List Of Values Under A Group

Aug 4, 2007

I have the following dataset:

State ZIP Homes Schools
WA 98007 2000 4
WA 98052 3000 5
WA 98079 2000 3

Now if I have set the group by expression on State but as display if I want to show it as €œ[98007, 98052, 98079]€? how can I accomplish this.

My report needs to show:
State Homes Schools
[98007, 98052, 98079] 7000 12


Any help will be greatly appreciated

View 6 Replies View Related

List Of Values In Input Parameter

May 8, 2008



Hello,
I have the following issue:

I have the following statement into a function:
select a,b form T where c IN @parameter

t is the table
c is datatype= integer
@parameter is a input parameter in the function, the @parameter contains more values and passed as a string.

Running the statement above I got an error due to conversion type.

How can I pass a list of parameters in the @parameter variable to make the statement works?

Thank in advance.

View 4 Replies View Related

Gettting Primary Key Values In A DML CREATE TRIGGER

Jun 10, 2008

I would like to get the primary key values in a DML CREATE TRIGGER as follows:
CustomerID = 12 AND InvoiceID = 50
I create the DML trigger for UPDATE and DELETE only, how do I retrieve the values as above ?
Jon Galloway provided a sample in http://weblogs.asp.net/jgalloway/archive/2008/01.aspx but the Primary Key Field and Primary Key Value were inserted into separate columns while I need to combine the Primary Key Field and Primary Key Value into 1 single field, using his example, I want to form the primary key as:
ScoreId = 423
ScoreId = 3064
etc.
Note that his example only show a single primary key in a table whereas I need to handle multiple primary keys in a table.
 

View 4 Replies View Related

Primary Key Means It Should Not Accept Null Values Is It?

Sep 15, 2004

Friends,

I am inserting a value from vb.net to sql server 7.
i created a table in the sqlserver with primary key.
In VB.net form i didnt check the values of the check box.
when i try to save the empty or null the sql server should throw the error. but its accepting the null value.
how is it possible?
can anyone help me?
FYI:
table name: JobMast
Fields:
jobcode int primary key
jobtitle varchar(10)

View 12 Replies View Related







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