Create An Array In A Result Field

Sep 12, 2006

I am between the "newbie" and "intermediate" stages of writing SQL code and I am wondering if there is a way to capture multiple results into one field so I can basically create a "set" for a unique identifier.  Here is few result samples I receive from this code I am using now.  
ReqNo              ProcID
7102005          1409
7102005          1796
7139003          1411
7139003          6097
7261030          1409
7261030          1796
7268303          3998
7268303          4000
 
I would like to create a single row for each "ReqNo" and have a field that will an array of the  "ProcID" results I receive.  In other words, for the first "ReqNo" 7102005, can I create a field that will combine the 1409, 1796 into one field?  I am trying to capture an array of integers used for that "ReqNo" so I can use that as a unique identifier in a join for another table. 
 
So, ideally my result would be:
ReqNo             ProcSet
7102005          1409, 1796
7139003          1411, 6097
7261030          1409, 1796
7268303          3998, 4000
 
Is this possible?


declare
@startdate smalldatetime,
@enddate smalldatetime ,
@month int,
@year int
 
select
 @startdate = dateadd (dd, -7, getdate())
SELECT
@month = datepart (month, @startdate),
@year = datepart (year, @startdate)
SELECT
@startdate = convert (smalldatetime, convert(varchar(2), @month) + "/1/" + convert (varchar(4), @year))
 
select
@enddate = dateadd (dd, 1 , @startdate)
 
select distinct
pp_req_no as ReqNo,
pp_cproc_id_r as ProcID
 
from
risdb_rch08_stag..performed_procedure
(index pp_serv_time_r_ndx)
 
where
pp_service_time_r between @Startdate and @Enddate
and pp_status_v = 'CP'
and pp_rep_id > 0
 
order by
pp_req_no, pp_cproc_id_r

 

View 4 Replies


ADVERTISEMENT

Result Set Into Array

Aug 22, 2004

Hi All,

How can I read a query result set (which are of type VARCHAR) into an array? i.e the result set comprises of just one column and 5 rows and i want to save these into an array to easily extract each row whenever i want to. Hope I have conveyed my idea clearly.

Gayathri

View 1 Replies View Related

Retrieve SQLQuery Result In Array On C#--How ????

Apr 9, 2008

 Hi I'am practically new in C#, so I want to ask you guys some question.Let say I have this query:"Select EmployeeID, EmpName from PI_Employee"How can I retrieve the result from EmployeeID column and EmpName column and put it into collections of array, so I can call the array and use it again in another function. Can I possibly do that in C# ? if so, how ? please help me guys, I'm on the edge of nervous wreck in here so I could use a little help, any kinds of help. Thanks. Best Regards. 

View 2 Replies View Related

Create An Array With T-SQL

Oct 10, 2007

I'm using SQL Server 2005 and have have a table similar to the following:

Name Week Role
Bob 1 Primary
Bob 2 Backup
Bob 3 Primary
Joe 1 Backup
Joe 2 Backup
Mike 1 Primary
Mike 2 Backup



Is there a t-SQL query that will combine the role and week into an array and return a table like:

Name Role
Bob Primary: 1, 3
Backup: 2
Joe Backup: 1, 2
Mike Primary: 1
Backup: 2

View 1 Replies View Related

Create Array Of Columns

Feb 18, 2008

Absolute beginner to SQL here...

I'm try to create an array of columns and then refer to these columns at the asp.net level.

Can't seem to find this in the SQL literature...so I'm probably looking at this all wrong.

Thanks in advance.

Larry

View 6 Replies View Related

What Type Of Field Should I Use To Store An Array Or Datatable?

Feb 25, 2005

Hi - I'm using Session variables to store information (sort of webshop).

The sessions are a mix of the usual straightforward strings, wich are no problem - but I also have a DataTable which I store in a session variable, and I also have an array, which I store in a session variable eg. session("day")(x) where x is the item in the array.

I want to give my users the ability to store the items they've selected in my database, but have no idea of what type of field I should use for the datatable and array session variable. Should I use a TEXT field, or is there another more appropriate one'?

I'm using SQL Server 2000.

Thanks for any help,

Mark

View 3 Replies View Related

Passing An Array And/or Variable Field Name To An SProc

Jul 20, 2005

I have 2 questions.I am trying to write a stored procedure to update a table. I am tryingto pass a variable that represents the name of the column/field andanother for the value that I am changing.For example:@FieldName VARCHAR(100)@FieldValue VARCHAR(100)ASUPDATE tblTHETABLESET @FieldName = @FieldValueFirst is it possible to use a variable as the column/field name? Ifso, how do I go about it?Also, it would be nice if I could have the @FieldName and @FieldValuevariables as arrays. Is that possible?Thank-you for any assistanceBill

View 2 Replies View Related

How To Create An Array In A Stored Procedure

Aug 8, 2000

I just want to Know how to create an Array in a stored procedure. Please can you give the syntax and any Example.

View 1 Replies View Related

Another Noob Question... What Field Type Is Best For Storeing An Array?

Feb 1, 2008

Is there a special field type I should be using, instead of a NVARCHAR for storing comma separated words?

I could, construct an array from the field value... but not sure if this would be the "right" way to do it?

View 4 Replies View Related

How To Create Array Column And How To Retrive In Sqlserver

May 9, 2005

hi
i am using database sqlserver,i am searching for varray concept like in oracle to store multiple values in a single column(as array column) like that shell i do in sql server
1.how to create array column in a table using sqlserver
if possible how can i use select query  for that

View 4 Replies View Related

RS2005: Export To Excel Error: Destination Array Was Not Long Enough. Check DestIndex And Length, And The Array's Lower Bounds.

Jan 25, 2007

All,

I am using Reporting Services 2005. One of my reports is getting the following error when I try to export to Excel. It will export to .CSV though.

"Destination array was not long enough. Check destIndex and length, and the array's lower bounds."

Any suggestions would be greatly appreciated. Please copy me at machelle.a.chandler@intel.com.

Machelle

View 10 Replies View Related

Create Date Field From Substring Of Text Field

Jul 20, 2005

I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark

View 2 Replies View Related

How Would I Send A String Array As A Integer Array?

Jun 25, 2007

I have a stored procedure that has a paramter that accepts a string of values. At the user interface, I use a StringBuilder to concatenate the values (2,4,34,35,etc.) I would send these value to the stored procedure. The problem is that the stored procedure doesn't allow it to be query with the parameter because the Fieldname, "Officer_UID" is an integer data type, which can't be query against parameter string type.
What would I need to do to convert it to an Integer array?
@OfficerIDs as varchar(200) 
Select Officer_UID From Officers Where Officer_UID in (@OfficerIDs)
 Thanks

View 5 Replies View Related

How Check Field - And Add Result Row At END

Mar 11, 2008

help
i need to check each column
and if i don't have the value
than add to a new result row


if not show the missing value

if i have in the field A B C than OK (like day1 + day2)

if i don't have the value A B C than than show it like this a|b|c (like day3)


if i don't have the value c than than show it like this -|-|c (like day4)



if i don't have the value a than than show it like this a|-|- (like day5)




if i don't have the value b than than show it like this -|b|- (like day6)

day1 day2 day3 day4 day5 day6
-------------------------------------------------------------------------
* c * a c a
* a * a c *
a b # b b c
b * $ Q c s
c # 0 0 0 *
---------------------------------------------------------------------new result row
ok ok a|b|c -|-|c a|-|- -|b|-
TNX

View 7 Replies View Related

Concatinating Two Field And Insert The Result

Aug 20, 2005

Hii,I need to concatinate two field and insert the result into each record. So far I managed to display the concatination but how do I insert it?use northwind
select city, region,([city]+ +[region]) as uniqefrom customerswhere region is not nullThe resulting records in Quary Anchorage AK AnchorageAKTsawassen BC TsawassenBCVancouver BC VancouverBCSan Francisco CA San FranciscoCA
 

View 1 Replies View Related

Checking Whether Particular Field Present In The Result Set

Jul 17, 2007

Hi,

I have one dymic report and one of the requirement is that,I want to check whether particular field is present there or not



For e.g In one of the case Fields!Name will come and in other case it won't.

There one dynmic column which calculated values based on all the column.



If I write in value field of this column as follows.[If status = "Name" and Field is not missing then take Name value,if field name is Consumer take value from Consumer and shows it in column.]



=Switch(Fields!portfolio_status.Value="Name",IIF(Fields!IName.IsMissing,"0",Fields!Name.Value)

,Fields!portfolio_status.Value="Consumer",IIF(Fields!Consumer.IsMissing,"0",Fields!Consumer.Value))





In case,portfolio_status doesn't give me value "consumer" then I expect "0" to get printed.Problem here is that IIF expression tries to evaluate Fields!Consumer.Value even if it is not using this value and because of that my calculated column is not working.

I tried IsMissing,IsNothing..Nothing seems work.Basically I want to check if that field is there in result set or not.



IIF(IsNothing(Fields!Consumer),"0",Fields!Consumer.Value)

IIF(Fields!Consumer.Value Is Nothing,"0",Fields!Consumer.Value)

View 1 Replies View Related

Create Union Query Result

Jun 20, 2008

Hi,I was wondering if someone can help me.  I have 4 tables in an SQL Server 2005 database, for purposes sake called 'table1', 'table2', 'table3' and 'table4'. They all have the same data structure between them.  The columns inside them have the exact same design.  For purposes sake called 'column1', 'column2', 'column3' and 'column4'.  Alot of these tables have duplicate records scattered between them, so basically I want to create 'table5' with the same column names but only unique records.  Can someone specify the syntax I need for this.  I'm pretty sure it's a union query I need so am scouring as I type this also.   Many Thanks Robert  

View 5 Replies View Related

SQL Query - Using Result Of Create Function

Aug 24, 2004

I created a function that will return
from OpenDataSource('.....') tablename
where ... is fully populated.

However, I can't figure out how to use it?

For example

select functiona (parameter) as data_src

this returns the "from" statement above

I then try to run

select * data_src

So how do I reference the contents of data_src in the select?

Thanks for any help

View 1 Replies View Related

SQL Server 2012 :: Updating A Field Based On Result Set

Feb 21, 2014

I am trying to update records based on the results of a query with a subquery.

The result set being produced shows the record of an item number. This result produces the correct ItemNo which I need to update. The field I am looking to update is an integer named Block.

When I run the update statement all records are updated and not the result set when I run the query by itself.

Below you will find the code I am running:

create table #Items
(
ItemNovarchar (50),
SearchNo varchar (50),
Historical int,
Blocked int

[Code] ....

Below is the code I am using in an attempt to update the block column but it updates all records and not the ones which I need to have the Blocked field set to 1.

Update #items set Blocked = 1
Where Exists
(
SELECT ItemNo=MAX(CASE rn WHEN 1 THEN ItemNo END)
--,SearchNo
--,COUNT(*)

[Code] ...

Why is the update changing each record? How can I change the update to choose the correct records?

View 6 Replies View Related

SQL Server 2012 :: Replace A Result Value Based On Value Of Another Field?

May 7, 2015

I would like to replace the value in the Select query of CO OWNER with a space ' ' or NULL if the ORDINAL value = 0.

The CO OWNER name is stored in the CARDNAME table, and the OWNER name is stored in the NAME table. I can not change the db structure.

JEAN is the ACCOUNT owner and BILL is CO OWNER of two cards.

Current Select Results:

ACCOUNT CARD ORDINAL CO OWNER OWNER
200500 9999999999999100 2 BILL JEAN
200500 9999999999999101 1 BILL JEAN
200500 9999999999999102 0 BILL JEAN

Desired Select Results:

ACCOUNT CARD ORDINAL CO OWNER OWNER
200500 9999999999999100 2 BILL JEAN
200500 9999999999999101 1 BILL JEAN
200500 9999999999999102 0 NULL JEAN

Current SQL Select statement:

SELECT DISTINCT
CARD.PARENTACCOUNT AS ACCOUNT,
CARD.NUMBER AS CARD,
CARD.ORDINAL,
CARD.STATUS,
CARDNAME.FIRST AS CO_OWNER,

[code]....

View 9 Replies View Related

Query On Datetime Field Giving Eroneous Result

Mar 20, 2008

Hi,

I am trying to fetch some data from a table .
I wanted to fetch data for which the order_date is >= 1st january 2005 and order_date< 10th January 2005 and for that I have a query

like
1>



SELECT * FROM SALES_ORDER WHERE


ORDER_DATE >= '01/01/2005' and ORDER_DATE <'10/01/2005'


In this query i am getting result for which the order_date is 2005-05-27 and lots of other data for which my criteria is not matching.
The Order_Date is a datetime field.

If I am use convert function then i am getting the expected result

like
2>
SELECT * FROM SALES_ORDER WHERE
convert (datetime, convert (varchar,ORDER_DATE,101), 101) >= '01/01/2005'
AND
convert (datetime, convert (varchar,ORDER_DATE,101), 101) < '01/10/2005'

Why the first query is not giving me correct result ?

View 4 Replies View Related

Create Table Structure From Select Result

Dec 21, 2006

Hi,
I need to create a table which has the columns from the select statement result.
I tried in this way
drop table j9a
SELECT er.* into j9a
FROM caCase c
LEFT OUTER JOIN paPatient pp ON c.caCaseID=pp.caCaseID
Left Outer JOIN paManagementSite pm ON pp.paManagementSiteID=pm.paManagementSiteID
Left Join exexposure ee ON ee.cacaseID=c.caCaseID
LEFT OUTER JOIN exExposureRoute eer ON eer.caCaseID=c.caCaseID
LEFT OUTER JOIN exRoute er ON er.exRouteID=eer.exRouteID
WHERE c.caCallTypeID =0
AND c.Startdate between '1/1/2006' and '12/1/2006'
AND (ee.exMedicalOutcomeID=4 OR ee.exMedicalOutcomeID=10)
AND pp.paSpeciesID=1
AND c.PublicID_adOrganization_secondary is null

declare @1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 int,@8 int,
@9 int,@10 int,@11 int,@12 int,@21 int,@22 int,@23 int,@24 int,@25 int,
@26 int,@27 int,@28 int,@29 int,@30 int,@31 int,@32 int

set @21=(select count(*) from j9a whereIngestion=1)
set @22=(select count(*) from j9a whereInhalation/nasal=1)
set @23=(select count(*) from j9a whereAspiration=1)
set @24=(select count(*) from j9a whereOcular=1)
set @25=(select count(*) from j9a whereDermal=1)
set @26=(select count(*) from j9a whereBite=1)
set @27=(select count(*) from j9a whereParenteral=1)
set @28=(select count(*) from j9a whereOtic=1)
set @29=(select count(*) from j9a whereRectal=1)
set @30=(select count(*) from j9a whereVaginal=1)
set @31=(select count(*) from j9a whereOther=1)
set @32=(select count(*) from j9a whereUnknown=1)

Create table table9(Route varchar(30),Fatal int)
insert into table9 values ('Route_Ingestion',@1,@21)
insert into table9 values ('Route_Inhalation',@2,@22)
insert into table9 values ('Route_Aspiration',@3,@23)
insert into table9 values ('Route_Ocular',@4,@24)
insert into table9 values ('Route_Dermal',@5,@25)
insert into table9 values ('Route_Bite',@6,@26)
insert into table9 values ('Route_Parenteral',@7,@27)
insert into table9 values ('Route_Otic',@8,@28)
insert into table9 values ('Route_Rectal',@9,@29)
insert into table9 values ('Route_Vaginal',@10,@30)
insert into table9 values ('Route_Other',@11,@31)
insert into table9 values ('Route_Unknown',@12,@32)

select * from table9

The exRoute result is like this
70 Ingestion
71 Inhalation
72 Aspiration
73 Ocular
74 Dermal
75 Bite/sting
76 Parenteral
77 Other
78 Unknown
524 Otic
525 Rectal
526 Vaginal

The above giving the errors
Msg 207, Level 16, State 1, Line 19
Invalid column name 'Ingestion'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'Inhalation'.

Thanks in advance

View 1 Replies View Related

Binding Report/Field 'language' Setting To Query/Parameter Result.

Oct 24, 2007

Hello,

I have a report which displays a customers invoice, in both the companys local currency, and the customers local currency.

The report language is "English (United Kingdom)"
The fields showing customers currency language setting is set to something else, i.e. "France (French)" to display the Euro currency.

The application handles 34 currencies, the query returns the language string, ("France (French)"), to allow the report to bind its language setting to the querys output.

However, it doesn't work, a normal textbox will display the correct country name string, but Reporting Services cannot bind the language setting to a query result. So I also tried setting it as a report parameter, but no joy either (all currencys revert to USD).

I'm using =First(Fields!curFormat.Value, "myDataSet") to bind the 'language' setting, the result of this expression returns "France (French)", which is a valid option for this language setting, as it's in the drop down list.

Rather than create 34 seperate reports for each currency, are there any suggestions on how to bind a fields language setting to a query result?

View 3 Replies View Related

Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field

Sep 20, 2007

I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.

Any help would be greatly appreciated.

Current Table

Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40

Proposed Table

Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008

Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008

Thanks,
Mike Misera

View 6 Replies View Related

Create A Query That Will Give Result Set Containing Primary Order On Type

May 14, 2012

I have a table with plant types and plant names. Certain plants are grouped on a custom field, currently called Field. I am trying to create a query that will give me a result set containing the primary order on Type, but need items with the same 'Field' value grouped by each other.For example, the following shows a standard query result with "order by Type", ie select * from plants order by Type

Code:
ID Type Name Field
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
5 Type5Name5(group2) -group2
6 Type6Name6(group6)

But I want it to look like this, with fields of the same value located next to each other in the result set (but still initially ordered by Type)

Code:
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
5 Type5Name5(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
6 Type6Name6(group6)

View 7 Replies View Related

Transact SQL :: Create A Pivot Query That Will Give Result That Is Horizontal

Jul 8, 2015

I have some data which is vertical...I want to create a pivot query in SQL that will give me a result that is horizontal like this. I cannot find a way of doing it without lots of IF or CASE statements?

View 10 Replies View Related

How To Create A RTF Field ?

Nov 29, 2006

How can I save the text of a rich text box in a sql express 2005 datatable ?

Of course I'd like to save the string and the format of the text (bold, color etc...).

Which column type I have to use for this RTF field ?

Thank you.

View 3 Replies View Related

Any Need To Create An Index For A PK Field?

May 6, 2008

Are Primary Key fields automatically indexed, or do you have to create a seperate index for a PK in order for it to be indexed? I'm using SQL Server 2005.

View 7 Replies View Related

Create Unique Field That Isn't The Key

Nov 10, 2005

I have a table where the key is an autonumber. I also have a field which holds the reference of a room eg 0BM1. It is nvarchar. Is there a way I can set this field to duplicates = No, so that my user cannot enter the same room reference more than once. Or do I have to do this check in my asp.net code ?

TIA

View 1 Replies View Related

How To Create An Aggregated Field

Apr 11, 2006

I have the following fields in table A:

GL_ID|GL_Name_VC | Amount |Period_TI|Year_SI
===================================================
1000| Inventory| 8,000.00 | 01 | 2005
===================================================
1000| Inventory| -3,000.00 | 02 | 2005
===================================================
1000| Inventory| 5,000.00 | 02 | 2005
===================================================


the fields above have the following datatype:

Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger

The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:

GL_ID | GL_Name_VC |Op Bal|Period_Dr|Period_Cr|Period Bal|Closing Bal
======================================================================
1000 | Inventory |8,000 | 5,000 | -3,000 | 2,000 |10,000

The above report has an Op Bal column which is the sum of all amount in Period 01 in
Year 2005 carried forward as opening balance in Period 02, Period_Dr Column would contain
all positive amount in Period 02 & Period_Cr Column would contain all negative amount
in Period 02. Period Bal is the summation of both Period_Dr & Period_Cr and Closing Bal
column is the summation of Op Bal + Period Bal.

Guys, hope someone out there can help me with the sql command for the above report?

View 1 Replies View Related

Create Linefeed In Field

Jul 20, 2005

Hello,I would like to create more lines by concatenating values.When I use: <select 'This' + ' ' + 'is' + ' ' + 'an' + ' ' +'example'> the result is <This is an example> (on the same line).I woul like to get:<Thisisanexample> (each 'word' on a new line, but in 1 field)Whis SQL statement do i have to use?

View 2 Replies View Related

How To Dynamic Create Bit Field On Sql

Apr 10, 2008



Hi

In Sqlserve we can create dynmic field on sql like:

Select '' as Name from Employee.

It creates a string field.

I want to how to create a Bit field in this way.

Regards
Deepak

View 1 Replies View Related

How To Create A Trigger To Update A Field

Aug 2, 2007

Hi -
I know my way around VS but I am just exploring "advanced" SQL Server 2005 and have run into a challenge which I think a trigger could solve, but I am not sure and also don't know how to set that up. So any help or links to tutorials are highly appreciated.
Here is the challenge: I have a table with a number of fields, among them RequestID (bigint) and Booktime (datetime). What I would like to happen is whenever someone writes a value different from NULL into RequestID, Booktime gets set to the current timestamp. When RequestID gets set to NULL, Booktime gets set to NULL.
Is there a way to do this with a trigger (or an otherwise elegant way)?
Thanks in advance for ANY help or ideas.
Oliver

View 3 Replies View Related







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