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