1:M Single Line Return

Jul 11, 2007

Just messing about with a silly little problem in SQL which has me stumped!
The problem goes like this;
There are lots of books.
There are lots of authors.
Every book has atleast one author (primary author).
Some books have a secondary author too!

Books(BookId, BookName)
Authors(AuthorId, AuthorName)
BookAuth(BookId, AuthorId, PrimaryAuth)
Where primary auth is a bit field; 1 denotes primary, 0 denotes secondary.

The problem is trying to return a single line per book - something like

BookNamePrimary AuthorSecondary Author
Book 1DaveNULL
Book 2JohnDave
Book 3LauraJohn

The following code is the little test script I've written which you may wish to utilise

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'Books') BEGIN
DROP TABLE Books
END
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'Authors') BEGIN
DROP TABLE Authors
END
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'BookAuth') BEGIN
DROP TABLE BookAuth
END

CREATE TABLE Books (bookid int, bookname varchar(50))
CREATE TABLE Authors (authorid int, authorname varchar(50) )
CREATE TABLE BookAuth (authorid int, bookid int, primaryauth bit)
GO

INSERT INTO Books
SELECT 1, 'MS Access Book'UNION ALL
SELECT 2, 'Microsoft Excel for Peanuts' UNION ALL
SELECT 3, 'Some Autobiography'UNION ALL
SELECT 4, 'SQL for Beginners'UNION ALL
SELECT 5, 'Advanced T-SQL'UNION ALL
SELECT 6, 'I can''t think of another title'

INSERT INTO Authors
SELECT 1, 'Dave'UNION ALL
SELECT 2, 'Steve'UNION ALL
SELECT 3, 'John'UNION ALL
SELECT 4, 'Joanne'UNION ALL
SELECT 5, 'Christina'UNION ALL
SELECT 6, 'Alex'

INSERT INTO BookAuth
SELECT 1,1,1 UNION ALL
SELECT 2,1,0 UNION ALL
SELECT 1,2,1 UNION ALL
SELECT 1,3,1 UNION ALL
SELECT 3,3,0 UNION ALL
SELECT 4,4,1 UNION ALL
SELECT 4,5,1 UNION ALL
SELECT 5,6,1 UNION ALL
SELECT 6,6,0

SELECT * FROM bookauth

DROP TABLE Books
DROP TABLE Authors
DROP TABLE BookAuth

It should be so easy - but I can't get it so far! :D

View 6 Replies


ADVERTISEMENT

How Can I Remove The Line Feed/carriage Return In The Last Line Of The Exported Text File ?

Feb 27, 2007

Hi,
for some AP issue, the file I upload must be without the line feed/carriage return in the last line.
for example:

original fixed-length file (exported from SSIS)
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
4 <-- with line feed/carriage return in the last line

The file format that AP request. The file only has 3 records, so it should end in the third line.
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90

Should I use script component to do it ? I am new for VB . Anyone would help me ?

Thank you all.

View 1 Replies View Related

How To Get All Same Rows In Single Line

Mar 6, 2013

i have a query in which i need to show all rows of same id in singel row and last column should be comma seperated .my query is :

BEGIN TRY
SET NOCOUNT ON
DECLARE @l_numberOfRecordsInserted INT
-- Check if valid Application User ID is passed
IF ( @i_AppUserId IS NULL ) OR ( @i_AppUserId <= 0 )

[code]....

so last column section name should come as comma seperated .

View 3 Replies View Related

DELETE In One Single Line...???

Sep 29, 2007

Hi,
I am executing the folowing statements in my stored procedure, I want to ask that can all these staments will be executed and combined in a single line of code, meaning can we run all these statemnts in a single line. if not, then any other optimized way to perform this task and other way which will execute with less load on the server.





Code Block

SET NOCOUNT ON
TRUNCATE TABLE WebNew_T_Dump
TRUNCATE TABLE WebNew_TOD_Dump
TRUNCATE TABLE WebNew_T_S_Dump
TRUNCATE TABLE WebNew_AT_Dump
TRUNCATE TABLE WebNew_MMM_Dump
TRUNCATE TABLE WebNew_MMT_Dump
TRUNCATE TABLE WebNew_TC_Dump
TRUNCATE TABLE WebNew_TRW_Dump
TRUNCATE TABLE WebNew_SM_Dump
TRUNCATE TABLE WebNew_TS_Dump
TRUNCATE TABLE WebNew_TL_Dump
TRUNCATE TABLE WebNew_TLC_Dump
TRUNCATE TABLE WebNew_TS_Dump
TRUNCATE TABLE WebNew_TP_Dump
TRUNCATE TABLE WebNew_TSS_Dump
TRUNCATE TABLE WebNew_IT_Dump
Thanks,

View 3 Replies View Related

Single Line DtsConfig Files

Apr 24, 2007

When I edit package configuration files inside of Visual Studio, it puts all the XML on a single line. Does anyone know how to automatically spread the information onto multiple lines, to make comparing file differentials easier.

Thank you,
Mike

View 6 Replies View Related

Return The Line Number Of The Error

Feb 4, 2004

i have a huge stored proc which has about 8-9 cursors which move data from a few temp tables to the final tables...and also do lots of calculations in between..

anytime there is an error in the stored proc i have an error page which looks like this

**********************
Server Error in '/WebApplication3' Application.
--------------------------------------------------------------------------------

Error converting data type varchar to bigint. 139 pdiscnum 37 3429 4978 139 139 4979 50.93 189.93 4980 -189.93 0 4981 139 139 4982 -139 0 4986 23 23 4987 0 23 4988 0 23 5121 139 162 5122 -328.93 -166.93 3430 4983 89 89 4984 -89 0 5096 89 89 5097 -178 -89 5135 89 0 5144 139 139 3431 4989 89 89 4990 -89 0 5100 89 89 5101 -178 -89 3432 4991 139 139 4992 4.32 143.32 4993 -143.32 0 5102 139 139 5103 -278 -139 3433 4994 139 139 4995 50.93 189.93 4996 8.64 198.57 4997 -198.57 0 5003 0 0 5123 139 139 5124 -328.93 -189.93 3434 4998 59 59 4999 -59 0 5000 59 59 5001 -59 0 5002 0 0 5041 19 19 5042 0 19 5043 -2.39 16.61 5044 -16.61 0 5045 19 19 5046 -2.39 16.61 5047 -16.61 0 5056 0 0 5084 39 39 5085 -5.85 33.15 5086 -49.76 -16.61 5104 59 42.39 5105 .........

************************************************

a very lengthy page...the numbers are prbly due to the print stmts of the results of some calculations...
but my q is..is there any way to return the xact line number where the error occured in the stored proc instead of these infinite list of numbers...its taking pretty long time to go through the entire stored proc to isolate the error...

thanks

dinakar

View 4 Replies View Related

Produces Multiple Lines, Need To Populate Single Line

Mar 11, 2008

I need to, ultimately, create a flatfile for exporting insurance information to a third-party vendor. Each individual is to have no more than 1 line per file, which contains their coverage information, if any, on 4 different type of insurance. What i need is for 2 fields in a table to determine the output for multiple fields in the flatfile.

What I have so far works, to an extent. If I have insurance types 1, 2, 4 (of types 1-4) required output is (__ = 2 blank spaces):



1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E

== == == == == == == == == == == == == == == == == == == ==

Y N __ MD XX Y N __ MD XX N __ __ __ __ Y N __ DN XX



If they have coverage, A always = Y, B always = N, C always = blank(null), D is their ins. type, E is their cov. type(CASE statement). if they DON'T have that type of coverage, A always = N and the remaining field are NULL.



After a lot of work, and scouring a forum or 2, I attempted a whole lot of CASE functions. Below is an sample of code representing the 1x statements. This same code is repeated 4 times with the 1x being altered with 2x, 3x, 4x.



CASE HB.PLAN_TYPE

WHEN '10' THEN 'Y'

ELSE 'N' END AS 1A,

CASE HB.PLAN_TYPE

WHEN '10' THEN 'N'

ELSE ' ' END AS 1B,

' ' AS 1C,

CASE HB.PLAN_TYPE

WHEN '10' THEN HB.BENEFIT_PLAN

ELSE ' ' END AS 1D,

CASE HB.PLAN_TYPE

WHEN '10' THEN (CASE WHEN HB.COVRG_CD ='1' THEN 'XX'

WHEN HB.COVRG_CD ='2' THEN 'YY'

WHEN HB.COVRG_CD ='3' THEN 'ZZ'

ELSE 'WW' END)

ELSE ' ' END AS 1E,



It works to an extent. While the desires/required output it above, the output this produces (same scenario) is:



1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E

== == == == == == == == == == == == == == == == == == == ==

Y N __ MD XX N __ __ __ __ N __ __ __ __ N __ __ __ __



1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E

== == == == == == == == == == == == == == == == == == == ==

N __ __ __ __ Y N __ MD XX N __ __ __ __ N __ __ __ __



1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E

== == == == == == == == == == == == == == == == == == == ==

N __ __ __ __ N __ __ __ __ N __ __ __ __ Y N __ DN XX



While there is supposed to be 1 line, regardless of number of insurance types, it only produces 1 line per type. I first tried this in Access, and was able to get it to work, but it required multiple queries resulting in a crosstab, export to Excel and manually manipulate the data, export to text file to manipulate, import back into Excel to manipulate, import back into Access and finally export into a flatfile. Far too much work to produce a file which is why I'm trying to convert it to raw SQL.
Any assistance in this matter would be greatly appreciated.

View 5 Replies View Related

Specifying Carriage Return/line Feed In An SQL Statement

Oct 19, 2001

Hi there, I hope you can help us.

We're using this statement to import some values into a database:
SQLStatement = "INSERT MyTable (ID, VALUE) VALUES ('" & IDString & "', '" & VALUEString & "')"

The problem is that the strings might contain carriage returns/line feeds, i.e.
VALUEString = "Line 1." & vbCrLf & "Line 2."

It only imports up until the first vbCrLf. We have tried replacing vbCrLf with "", but this doesn't work either. What is the correct format for inserting a value that contains new line characters?

Thanks for your time,
Lasse.

View 3 Replies View Related

Double Line Border Turn To Be Single In Reporting Service

Mar 6, 2007

I tried to set double in bottom border of text file. But it turned out to be single line either Previewing in Designer or Printing or Exporting to PDF. Does anyone have any idea?

View 10 Replies View Related

Native Compiler Return Value: [BC30494] Line Is Too Long

Feb 2, 2007

I'm developing a very big and complex P&L report in RS2005 for a big bank which suppose to have 20 tables with around 12 groupings per table. This is similar like having or combining 20 reports in one big report with massive data from Analysis Services 2005 data source.

The report was successfully compiled only up to the fourth table. When I added the fifth table by copying and pasting from the fourth table and then compiled the report in Dev Studio, I've got the following error message:

An error occurred during local report processing. The definition of the report '/Profit and Loss' is invalid. An unexpected error occurred while compiling the expressions. Native compiler return value: '[BC30494] Line is too long.'.

I have carefully made sure that the fifth table has exactly the same definition as the fourth table which I copied from and which was successfully compiled.

The rdl file size is around 2.5MB with only 5 tables and if I eventually adds up to 20 tables I would expect the rdl file size could be around 10MB, which is quite large compared to normal reports.

Even though it seems does not make sense, I suspect the rdl file size getting too big for the compiler to compile the report.

Is there any maximum rdl size limit that RS2005 compiler can compile? Or any idea why this error happens?

Thanks

View 4 Replies View Related

Adding Carriage Return And Line Feeds To A Text Box

May 7, 2007

Hi All,

I am trying to print 10 values in a single cell in a text box.....

To make it readable I put carriage returns and line feeds into the boxes.

These rendered ok in visual studio but did not render ok in explorer....no carriage return or line feed.



This is what I added...

=Fields!segment_sdesc_00.Value & chr(10) & chr(13)



Can anyone advise me as to what is wrong with this? There must be a way to put a cr/lf or newline into a report in a text box.....



Thanks in Advance



Peter

www.peternolan.com

View 6 Replies View Related

SQL Server 2012 :: Displaying Code Of A Stored Procedure To A Single Line

Sep 14, 2015

Any better way to query SQL 2012 to display the code of a stored proc to a single line. I'm trying to write a script to insert the contents of the procs between my devestprod environments. So people can query a single table for any proc that is different between environments. At the moment I am using the syscomments view and the text column but the problem here is if you get a lengthy proc it cuts it up into multiple rows.

I can get around it by converting the text to a varchar(max) and outer joining the query, but as you can see by my code below I have to try and guess what the maximum number of rows I'm going to get back for my largest proc. If someone adds a new one that returns 8 rows I'm going to miss it with this query.

Select col1.[type],col1.[name],convert(varchar(max),col1.text) + isnull(convert(varchar(max),col2.Text),'')
+ isnull(convert(varchar(max),col3.Text),'')
+ isnull(convert(varchar(max),col4.Text),'')
+ isnull(convert(varchar(max),col5.Text),'')
+ isnull(convert(varchar(max),col6.Text),'')
+ isnull(convert(varchar(max),col7.Text),'')

[Code] .....

View 3 Replies View Related

SSRS Removes Spaces And Line Breaks From Single Field Report

Jan 2, 2008



I am storing formatted data (including spaces and line breaks) in a single field in a table.
When I run a report on that field, the preview of the report is automatically removing all the extra spaces and line breaks, making the report unreadable.
When exporting the report to PDF or printing it, it shows the line breaks and spaces as expected.

Does anyone know how to make the report preview show the spaces and line breaks?

View 7 Replies View Related

Replace Carriage Return And Line Feed In A Text Column?

May 18, 2004

Hi,

I've a text column (text datatype) that contains carriage return and line feed.

Syntax-wise, how can I replace these by a space?

Thanks.

View 1 Replies View Related

Remove The Last Carriage Return And Line Feed From Sql Text Field

Jun 12, 2007

I am trying to write a user defined function that will allow me tostrip off the last carriage return and line feed from a text field.We have address fields stored in a text field for our ERP system andsome of them have an extra carriage return and line feed at the end ofthem. This causes havoc when we sync between our ERP system and CRMsystem. If anyone knows a way to solve this problem the help would beappreciated.Examples:Existing Text field with CR:1234 Blah Street<CR>Suite 2345<CR>Corrected Text field:1234 Blah Street<CR>Suitr 2345

View 4 Replies View Related

Return Single Value

Dec 3, 2006

Hello, I created a stored procedure which selects a value according to ContentId. I know that will be only one value returned or none. So if a record is found I want to return the string contained in ContentHtml. Else I want to return the string "NotFound" Could somebody help me out with this? Here is my present stored procedure: -- Specifies the SQL-92 equals compliant behavior SET ANSI_NULLS ON GO -- Specifies the SQL-92 quotation mark rules SET QUOTED_IDENTIFIER ON GO -- Alter procedure ALTER PROCEDURE [dbo].[by27_Content_GetContent]         -- Define the procedure parameters   @ContentName NVARCHAR(100),   @ContentCulture NVARCHAR(5) AS -- Prevent extra result sets from interfering with SELECT statements. SET NOCOUNT ON; -- Declare and define ContentId DECLARE @ContentId UNIQUEIDENTIFIER; SELECT @ContentId = ContentId FROM dbo.by27_Content WHERE ContentName = @ContentName -- Check if ContentId is Not Null IF @ContentId IS NOT NULL   BEGIN                 -- Select localized content from by27_ContentLocalized                 SELECT dbo.by27_ContentLocalized.ContentHtml                 FROM dbo.by27_Content     INNER JOIN dbo.by27_ContentLocalized       ON dbo.by27_Content.ContentId = dbo.by27_ContentLocalized.ContentId                         WHERE (dbo.by27_ContentLocalized.ContentCulture = @ContentCulture AND dbo.by27_Content.ContentName = @ContentName);   END -- Create procedure GO Thanks, Miguel

View 1 Replies View Related

Transact SQL :: FOR XML To Write Data With Carriage Return / Line Feed At End Of Elements?

Sep 30, 2015

I am trying to use FOR XML under SQL Server 2014 to write out a large XML data set. I want it to look like

<CVS_Member_Add_Change>
    <RecordType>3</RecordType>
    <Carrier>1266</Carrier>
    <MultiBirthCode>0000000</MultiBirthCode>
    <MemberType></MemberType>

[Code] ....

That's how it looks when you click on the results of a small subset of the query.  Just what I want.  Unfortunately when you try to right click and save it you get 

<dataroot><CVS_Member_Add_Change><RecordType>3</RecordType><Carrier>1266</Carrier<MultiBirthCode>0000000</MultiBirthCode><MemberType></MemberType<LanguageCode>1</LanguageCode><DURFlag></DURFlag><DURKey></DURKey><SocialSecurityNumber>000000000</SocialSecurityNumber</CVS_Member_Add_Change>

Everything being on one line blows up the translator application that reads the data.

The FOR XML statement copied out of the query is below.

FOR XML RAW ('CVS_Member_Add_Change'), ROOT('dataroot'), ELEMENTS 
GO

Is there a way in the T-SQL to force it to break lines neatly?

Is there a way to force it to a specific file name or directory?

View 3 Replies View Related

SQL Server 2008 :: Carriage Return Line Feeds In Flat File Imports

May 28, 2015

I'm in the process of importing a series of flat files into SQL Server. I'm using a ~ to separate the columns and the row delimiter is {CR}{LF}. One of the files has a field that contains the CRLF combination in a few places so that field is split over several rows. This is readily visible when I look at the flat file. However, when I'm importing the file, the Import and Export wizard seems to ignore them and import the files as they should with one row per record.

View 0 Replies View Related

SSIS Flat File Connection Manager Not Seeing Carriage Return-line Feed

Apr 29, 2006

I have a csv file as follows:

"100,002.01","200,00.01",10.98,aaaaaaa,bbbbbbbbbb
"100,002.01","200,00.01",10.98,aaaaaaa,bbbbbbbbbb
"100,002.01","200,00.01","1,000.98",aaaaaaa,bbbbbbbbbb

Note that the third column in the third line is also qualified by quotes whereas the previous two are not. I think this is because of Excel formatting. Is there any way to import this file correctly?

My main problem is that I never know whether a column will be qualified or not because this depends on the value. I need to loop through and import many of these files so a manual workaround is not a option for me.

View 22 Replies View Related

Return Single Value From Stored Procedure

Jul 10, 2006

   I am have stored procedure that accepts one parameter and returns a single value
The sp works fine.
My question is how do I set the c# webpage to return the single value.
I need a specific example of how to code.
this is how the code should work.
A blank form is loaded, when the user clicks submit I need the code to pull the Login name from the text box and check to see if it exist. I have the sp working the way I need it to. But I can figure how to pull the single value into a variable in the code behind.
I am using dataset elsewhere on the site, so I created a ds the calls the sp. Can it work this way?
thanks,
 

View 3 Replies View Related

Stored Proc How To Return A Single Value

Oct 9, 2007

How do I return a value in a stored procedure? I want to return a value for TheQuarterId below but under all test conditions am only getting back a negative one. Please help! create PROCEDURE [dbo].[GetQuarterIdBasedOnDescription]
(
@QuarterString nvarchar(10),
@TheQuarterId int output
)
AS

BEGIN
SELECT @TheQuarterId = QuarterId from Quarter WHERE Description=@QuarterString
END

 

View 1 Replies View Related

Return Multiple Rows As A Single Row

Oct 8, 2014

I’m trying to return data in a single row.Here’s what my table looks like:

Employee #,Hours Type,Total Hours
1234,Regular,40
1234,OT,8
1234,Regular,36

[code]....

I need the results of my query to total each hours type and group together:

EmpNo,Sum Of Regular, Sum of Overtime,Sum of Doubletime
1234,76,19,12
7777,45,8,5

I don’t know how to get the data returned in a single row.

View 2 Replies View Related

Return Multiple Rows Into One Single String

Nov 1, 2005

Hi,

I need to return multiple rows into one single string

Declare @String varchar(1000)

Create table Cus (CusId Int,CusName varchar(10))

Insert into Cus Select 1,'John'
Union All
Select 2,'Bob'

Select * from Cus returns

1John
2 Bob

I need to return the all the rows from Cus table into a single string. The return is dynamic.
I do not know the number of rows returned

My result should be

@String = 1,John,2,Bob

How can i do that ?

View 2 Replies View Related

SP Return Multiple Records For A Single Record

May 3, 2007

I have two tables
TermID, Term
1--- Abc
2--- Test
4--- Tunic

and
TermID, RelatedTermID
1 --- 2
1--- 4
2--- 4

I need to get back something like this

TermID, Term, RelatedTermsInformation
1--- test--- test,tunic#1,4


that above was my solution, get the relatedterms information and comma separate, and then put a # and get all the ids comma separate them and then put the in one field. then I can later parse it in the client

this does not seem like a very good solution ( or is it?)
If posible it would be nice to get something like this

TermID, Term, RelatedTermsInformation
1 test RelatedTermsTwoDimentionalArray

but I am not sure how this idea could be implemented using the capabilities of SQL.

my other option is have the client make one call to the database to get the terms and then lots of another calls to get the relatedTerms, but that will mean one trip to the DB for the list term, and one call for every single term found.

any ideas in how to make this better ?

View 8 Replies View Related

How To Return Value From Multiple Rows In A Single String

Oct 7, 2007



I have a table having Style Nos (VarChar Col), how I can return values from multiple rows in a single string.

for Example if table is having 3 records :-

1. Style 1
2. Style 2
3. Style 3

It should return single value in this way

Style 1, Style 2, Style 3


View 10 Replies View Related

How To Return Multiple Columns As Single Column

Mar 9, 2008

I have FirstName,LastName columns in the database.I need to return FirstName,LastName as Name to client(as a single column).

View 3 Replies View Related

Return Single Records By Joining Multiple Tables

Jun 4, 2008

I would like to know if it's possible to return a single record by joining the tables below. [Persons]
PersonID [int] | PageViewed [int]
=============== =================
1 10
2 5
3 2
4 12


[PersonNames] - PersonID JOINS Persons.PersonID
PersonID [int] | NameID [int] | PersonName [nvarchar] | PopularVotes [int]
=============== ============== ======================= ===================
1 1 Samantha Brown 5
1 2 Samantha Green 10
2 3 Richard T 10
3 4 Riko T 0
4 5 Sammie H 0


[AltNames] - backup for searches caused by common spelling mistakes
AltNameID [int] | AltNames [nvarchar]
================ =============================
1 Sam, Samantha, Sammie, Sammy
2 Riko, Rico


[PersonAllNames] - JOINS [PersonNames.NameID] ON [AltNames.AltNameID]
NameID [int] | AltNameID [int]
============= ================
1 1
4 1
3 2 
This is ideally what I'd like to have returned: PersonID | PageViewed | MostPopularName | NameSearch
========= ============ ================= =================
1 10 Samantha Green Samantha Brown, Samantha Green, Sam, Samantha, Sammie, Sammy
2 5 Richard T Richard T
3 2 Riko T Riko T, Riko, Rico
4 12 Sammie H Sammie H, Sam, Samantha, Sammie, Sammy
 
 
[MostPopularName] is [PersonNames.PopularVotes DESC].[NameSearch] combines all records from [PersonNames.PersonName] and [AltNames.AltNames].
 
The purpose for this is that I'd like to cache the results table so that all searches can just perform a lookup against the NameSearch field.
Any help would be greatly appreciated.
Thanks, Pete.

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

Stored Procedure For A Return Result Single Row With Coma(,) Seprator

May 1, 2008

Hello,
 
In SQL Server SELECT query Result displays tablePlease
tell how to create stored procedure for a single row at a time i.e. all only ONE row is
displayed at a time exselect name from useswhere name is display is tabular format,but i need in row with coma anc,d,f,f,e,g,h,jin this wayhow can i write a stored procedure for that    
 

View 4 Replies View Related

SQL Server 2012 :: Stored Procedure - How To Return A Single Table

Nov 21, 2013

I have this SP

ALTER PROCEDURE GetDelayIntervalData(@start datetime, @stop datetime, @step int)
AS
DECLARE @steps bigint
SET @steps = DATEDIFF(hour, @start, @stop)/ @step
DECLARE @i bigint
SET @i=0

[Code] ....

View 1 Replies View Related

T-SQL (SS2K8) :: Return Single Row For Matching Columns Based On 3rd Column

Sep 3, 2014

I have data:

Ticket User Priority
A ME 1
B ME 1
C ME 2
C ME 3
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
H YOU 3
I ME 1

Essentially if Ticket and User are the same I just want the min priority returned.

SO:
Ticket User Priority
A ME 1
B ME 1
C ME 2
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
I ME 1

I've tried partition and rank but can't get it to return the right output.

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

How Do I Return All Rows Value In A Single Column Delimit With Comma Separation?

Oct 23, 2007

Dear all,

I have a table like:

State -- Customer
TN -- AAA
TN -- AAA1
TN -- AAA2
Delhi -- BBB
Delhi -- BBB1
Delhi -- BBB2
Mumbai -- CCC
Mumbai -- CCC1
Mumbai -- CCC2
Maharashtra -- DDD
Maharashtra -- DDD1
Maharashtra -- DDD2

I want to show the output in a single query like:

State -- Customers
TN -- AAA, AAA1, AAA2
Delhi -- BBB, BBB1, BBB2
Mumbai -- CCC, CCC1, CCC2
Maharastra -- DDD, DDD1, DDD2


How do i do this in a single query

Can Pivot query will help in this situation. Please explain with a sample query

Thanks
gopalan@sofist.com
okugops@hotmail.com

View 4 Replies View Related







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