MS SQL Server :: Displaying Multiple Records From One Record

Combining Multiple Records Into One Record

Dec 20, 2007

Hi All,

I'm trying to develop a query that joins one record from a table with multiple matching records from another table all in one record,
Table1 has the primary key
id
--
1
2
3
4
Table2 has the follwing records
id year subject
-----------------
1 2000 English
1 2002 French
2 2004 English
2 2005 English
2 2006 English
3 2007 French
I want the result to be like this
id 2000 2001 2002 2003 2004 2005 2006 2007
-----------------------------------------------------------
1 English null French null null null null null
2 null null null null English English English null
3 null null null null null null null English

Appretiate your assistance

View 4 Replies View Related

Insert Multiple Records - First Record Is Insterted Twice: Why?

May 5, 2008

Hi and thanks for any advice.Right now I have a for loop that inserts multiple records. The first record is inserted into the database and I am not sure why. Here is the code I am using - Dim intPhotoKeyID As Integer
Dim InsertCmd As String = "Insert into Photos (OriginalName, GalleryID, PhotoDesc "
InsertCmd += ") values " & _
"(@OriginalName, @GalleryID, @PhotoDesc " & _
") "
InsertCmd += "; SELECT CAST(scope_identity() AS int);"

Dim DBConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("SqlConnGalleries").ConnectionString)
Dim MyCommand = New SqlCommand(InsertCmd, DBConnection)
MyCommand.Connection.Open()
MyCommand.Parameters.Add(New SqlParameter("@OriginalName", SqlDbType.VarChar, 150))
MyCommand.Parameters.Add(New SqlParameter("@GalleryID", SqlDbType.Int))
MyCommand.Parameters.Add(New SqlParameter("@PhotoDesc", SqlDbType.Text))
myCount = myCount & i 'Is zero for first two records
MyCommand.Parameters("@OriginalName").Value = "Orig" & i
MyCommand.Parameters("@GalleryID").Value = 5

MyCommand.Parameters("@PhotoDesc").Value = MyCommand.CommandText & myCount

Try
intPhotoKeyID = Convert.ToInt32(MyCommand.ExecuteScalar())
MyCommand.Connection.Close()
Catch Exp As Exception
Response.Write(Exp)
'ResultsLabel.Text = Exp.ToString()
End Try
DBConnection.Close() Below is the whole procedure which either uploads an image or directory of images and resizes them. Then gets the meta data from the image. Then creates an entry into the database for each image. If I am dealing with 4 images then 4 images are uploaded to the new gallery folder. But 5 entries are added to the database. Thanks again for any help, Jennifer Protected Sub Upload(ByVal sender As Object, ByVal e As EventArgs)

If ListDirectories.SelectedValue.Length > 0 Or fileUpEx.HasFile Then
Dim i As Integer
Dim selectedDirectory As String = ListDirectories.SelectedValue
Dim photoDescription As String
Dim photoAuthor As String
Dim photoTitle As String = ""
Dim photoName As String
Dim myFiles As String()
Dim fileCount As Integer

If multiUpload.Visible = True Then
myFiles = Directory.GetFiles(Server.MapPath(".") & "/tempimages/" & ListDirectories.SelectedValue & "/")
fileCount = myFiles.Length - 1
Else
fileCount = 0
End If

'Get last photo order from Photo table
Dim PhotoMaxOrder As Integer = 0
Dim DS As DataSet ' DataSet object
Dim SQL As String = "SELECT MAX(PhotoOrder) FROM Photos WHERE GalleryID=" & Request("ID").Trim
Dim connString As String = ConfigurationManager.ConnectionStrings("SqlConnGalleries").ConnectionString
Dim sqlDA = New SqlDataAdapter(SQL, connString)
DS = New DataSet
sqlDA.Fill(DS, "Photos")
If Not DS.Tables("Photos").Rows(0).Item(0) Is System.DBNull.Value Then
If DS.Tables("Photos").Rows.Count > 0 Then
PhotoMaxOrder = Convert.ToInt32(DS.Tables("Photos").Rows(0).Item(0)) + 1
End If
Else
PhotoMaxOrder = 1
End If

For i = 0 To fileCount

If multiUpload.Visible = True Then
photoName = Right(myFiles(i), InStr(StrReverse(myFiles(i)), "/") - 1)
Else
photoName = fileUpEx.PostedFile.FileName
End If

If InStr(photoName, ".jpg") > 0 Then
Dim MyPhoto As Bitmap

If multiUpload.Visible = True Then
MyPhoto = New Bitmap(myFiles(i))
Else
MyPhoto = Bitmap.FromStream(fileUpEx.PostedFile.InputStream)
End If
'testFile = myFiles(i)
Try 'Get photo description
Dim Make As PropertyItem = MyPhoto.GetPropertyItem("270")
Dim ascii As Encoding = Encoding.ASCII
photoDescription = ascii.GetString(Make.Value, 0, Make.Len - 1)
Catch ex As Exception
photoDescription = ""
End Try

Try 'Get photo author
Dim Make As PropertyItem = MyPhoto.GetPropertyItem("315")
Dim ascii As Encoding = Encoding.ASCII
photoAuthor = ascii.GetString(Make.Value, 0, Make.Len - 1)
Catch ex As Exception
photoAuthor = ""
End Try

Dim photoXmpData As String = GetXmpXmlDocFromImageStream(MyPhoto)

If Not photoXmpData = "" Then
photoTitle = GetXmpXmlNode(photoXmpData)
End If

'insert photo record into photo table

Dim intPhotoKeyID As Integer
Dim InsertCmd As String = "Insert into Photos (OriginalName, GalleryID, PhotoDesc "
InsertCmd += ") values " & _
"(@OriginalName, @GalleryID, @PhotoDesc " & _
") "
InsertCmd += "; SELECT CAST(scope_identity() AS int);"

Dim DBConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("SqlConnGalleries").ConnectionString)
Dim MyCommand = New SqlCommand(InsertCmd, DBConnection)
MyCommand.Connection.Open()
MyCommand.Parameters.Add(New SqlParameter("@OriginalName", SqlDbType.VarChar, 150))
MyCommand.Parameters.Add(New SqlParameter("@GalleryID", SqlDbType.Int))
MyCommand.Parameters.Add(New SqlParameter("@PhotoDesc", SqlDbType.Text))
myCount = myCount & i 'Is zero for first two records
MyCommand.Parameters("@OriginalName").Value = "Orig" & i
MyCommand.Parameters("@GalleryID").Value = 5

MyCommand.Parameters("@PhotoDesc").Value = MyCommand.CommandText & myCount

Try
intPhotoKeyID = Convert.ToInt32(MyCommand.ExecuteScalar())
MyCommand.Connection.Close()
Catch Exp As Exception
Response.Write(Exp)
'ResultsLabel.Text = Exp.ToString()
End Try
DBConnection.Close()

'check photo width and height
Dim NewFilePath As String = Server.MapPath("/appscode/galleries/photos/g" & Request("ID").Trim & "/") & "p" & intPhotoKeyID.ToString & ".jpg" ' & photoName
lblMessage2.Text = lblMessage2.Text & InsertCmd
If MyPhoto.Width.ToString = "200" Or MyPhoto.Height.ToString = "200" Then
'just copy the image
If multiUpload.Visible = True Then
File.Copy(myFiles(i), NewFilePath, True)
Else
fileUpEx.SaveAs(NewFilePath)
End If
Else
'resize image
Dim NewSize As System.Drawing.Size = New System.Drawing.Size(200, 200)
ResizePicture(MyPhoto, NewFilePath, NewSize) 'and save it
End If
MyPhoto.Dispose()
GC.Collect()
End If

Next
If Not myFiles Is Nothing Then
If myFiles.Length > 0 And multiUpload.Visible = True Then
'delete directory that was just processed
Directory.SetCurrentDirectory(Server.MapPath("."))
Directory.Delete(Server.MapPath(".") & "/tempimages/" & ListDirectories.SelectedValue & "/", True)
ListDirectories.Items.Remove(selectedDirectory)
'lblMessage2.Text = "Your file(s) have been added."
End If
Else
'lblMessage3.Text = "Your file has been added."
End If

Else
lblMessage2.Text = "Please select a folder."
End If

lblMessage3.Text = myCount
End Sub

View 8 Replies View Related

T-SQL (SS2K8) :: How To Change Multiple Records In One Record

Apr 14, 2015

View 8 Replies View Related

T-SQL (SS2K8) :: Only One Record From Table With Multiple Records?

Sep 4, 2015

I have a scenario where ID has three flags.

For example

ID flag1 flag2 flag3
1 0 1 0
2 1 0 0
1 1 0 0
1 0 0 1
2 0 1 0
3 0 1 0

Now I want the records having flag2=1 only.. I.e ID=3 has flag2=1 where as ID = 1 and 2 has flag1 and flag3 =1 along with flag2=1. I don't want ID=1 and 2.

I can't make ID unique or primary. I tried with case when statements but it I am somehow missing the basic logic.

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

Easiest Way Of Combining Multiple Fields From Different Records Into One Record?

Jul 20, 2005

I have a table;CREATE TABLE theLiterals (theKey varchar (255) NOT NULL ,theValue varchar (255) NULL)INSERT INTO theLiterals VALUES('defaultServer','\MyServer')INSERT INTO theLiterals VALUES('defaultShare','MyShare')INSERT INTO theLiterals VALUES('defaultFolder','MyFolder')INSERT INTO theLiterals VALUES('defaultFile','MyFile.dat')I then try;SELECTdefaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END,defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END,defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END,defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue ENDFROM theLiteralsand I get;defaultServer defaultShare defaultFolder defaultFile\MyServer NULL NULL NULLNULL MyShare NULL NULLNULL NULL MyFolder NULLNULL NULL NULL MyFile.datbut I want it COALESCEd like this;defaultServer defaultShare defaultFolder defaultFile\MyServer MyShare MyFolder MyFile.dat....but my syntax is incorrect. Is there an efficient way of doing this.I want to have a script/UDF where I can say...GetLiteralsFor('defaultServer','defaultShare','def aultFolder','defaultFile')and then my one-row recordset will be...RS(0) will = '\MyServer'RS(1) will = 'MyShare'RS(2) will = 'MyFolder'RS(3) will = 'MyFile.dat'Thanks for any help!

View 5 Replies View Related

SQL Server 2012 :: Multiple Records Queried Into Single Record?

Jan 27, 2014

I have 2 tables People and Scores. A person might have 1-5 scores (unknown at time of Query). I would like to query the two tables into a results table and if person does not have a record the score will be zero. Scores also have a test number so you know which score it is. I can get it done with Stored Proc but I have to use Temp tables and then put the temp tables together.

People
Name ID
Tom5
Dick2
Harry3
Larry4
Curly1
Scores
PrimaryKeyPeopleIDScoreTestNumber
12801

[code]....

Results

PrimaryKeyPeopleIdScore1Score2Score3Score4Score5Name
1110090807090Curly
22800000Dick
33909010000Harry
44507090900Larry
559070000Tom

View 8 Replies View Related

SQL Server 2008 :: Combining Multiple-same Records With Different Values Into One Record

Jun 25, 2015

I have a question about combining multiple records with same ID but different values between records on different fields into a single record with all values combined for all fields. Example: Multiple records with the same record ID:

RECORD_ID BO_ID Code Code_Date SubMsgCD1 SubMsgCD2 LNMsgCD1
1380900 XZ01 12 1/6/2015 P302
1380900 XZ01 12 1/6/2015 L405
1380900 XZ01 12 1/6/2015 P302 1004

INTO a single record:

RECORD_ID BO_ID Code Code_Date SubMsgCD1 SubMsgCD2 LNMsgCD1
1380900 XZ01 12 1/6/2015 P302 L405 1004

View 2 Replies View Related

Transact SQL :: Use FOR XML To Return Multiple Child Records Within Each Parent Record

Aug 7, 2015

I have a single complex query.

SELECT
Col1, -- Header,
Col2, -- Header,
Col3, -- Detail
Col4, -- Detail
Col5, -- Detail
FROM
TableName;

The query repeats the Header row value for all children associated with the header.I need the output of the query in XML format such that..For every Header element in the XML, all its children should come under that header element//I am using -

SELECT
Cols
FROM
Table Names
FOR XML PATH ('Header'), root('root') , ELEMENTS XSINIL

This still repeats the header for each detail (in the XML) , but I need all children for a header under it.I basically want my output in this format -

<Header >
<detail 1>
</detail 1>
<Detail 2>
</Detail 2>
<detail 3>
</detail 3>
</Header>

View 2 Replies View Related

T-SQL (SS2K8) :: How To Split One Record Into Multiple Records In Query Based On Start And End Date

Aug 27, 2014

I would like to have records in my Absences table split up into multiple records in my query based on a start and end date.

A random record in my Absences table shows (as an example):

resource: 1
startdate: 2014-08-20 09:00:00.000
enddate: 2014-08-23 13:00:00.000
hours: 28 (= 8 + 8 + 8 + 4)

I would like to have 4 lines in my query:

resource date hours
1 2014-08-20 8
1 2014-08-21 8
1 2014-08-22 8
1 2014-08-23 4

Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.

What I now get is:

resource date hours
...
1 2014-08-19 NULL
1 2014-08-20 28
1 2014-08-21 28
1 2014-08-22 28
1 2014-08-23 28
1 2014-08-24 NULL
...

... instead of the correct hours per date (8, 8, 8, 4).

A very simplified extract of my code is:

DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2014-01-01'
SET @enddate = '2014-08-31'
SELECTh.res_id AS Resource,
t.datevalue,
(SELECT ROUND([dbo].[HRCapacityHours] (h.res_id, t.datevalue, t.datevalue), 2)) AS Capacity,
(SELECT [dbo].[HRAbsenceHours] (9538, h.res_id, t.datevalue, t.datevalue + 1) AS AbsenceHours
FROMResources h (NOLOCK)
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (@startdate, @enddate)) t

p.s.The 9538 value in the HRAbsenceHours function refers to the absences-workflowID.I can't get this solved.

View 1 Replies View Related

Results Produce A Single Record Based Off Of Parameters. Want To Change It So It Returns Multiple Records.

Dec 20, 2007

I have a query that will return one record as its results if you provide two variables: @login and @record_date. This works great if you only want one result. However, now what I want to do is not provide those variables and get the result set back for each login and record_date combination. The hitch is that there are several other variables that are built off of the two that are supplied. Here is the query:

DECLARE @login char(20), /*This sets the rep for the query.*/
@record_date datetime, /*This is the date that we want to run this for.*/
@RWPY decimal(18,2), /*This is the required wins per year.*/
@OCPW decimal(18,2), /*This is the opportunities closed per week.*/
@OACW decimal(18,2), /*This is opportunities advanced to close per week.*/
@TOC decimal(18,2), /*This is the total number of opportunities in close.*/
@OANW decimal(18,2), /*This is opportunities advanced to negotiate per week.*/
@TON decimal(18,2), /*This is the total number of opportunities in negotiate.*/
@OADW decimal(18,2), /*This is the opportunities advanced to demonstrate per week*/
@TOD decimal(18,2), /*This is the total number of opportunities in demonstrate.*/
@OAIW decimal(18,2), /*This is the opportunities advanced to interview per week.*/
@TOI decimal(18,2), /*This is the total number of opportunities in interview.*/
@OCW decimal(18,2), /*This is the opportunities created per week.*/
@TOA decimal(18,2) /*This is the total number of opportunities in approach.*/

SET @login = 'GREP'
SET @record_date = '12/18/2007'
SET @RWPY = (SELECT ((SELECT annual_quota FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)/(SELECT target_deal FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)))
SET @OCPW = (SELECT @RWPY/weeks FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OACW = (SELECT @OCPW/cls_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOC = (SELECT @OACW*(cls_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OANW = (SELECT @OACW/neg_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TON = (SELECT @OANW*(neg_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OADW = (SELECT @OANW/dem_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOD = (SELECT @OADW*(dem_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OAIW = (SELECT @OADW/int_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOI = (SELECT @OAIW*(int_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OCW = (SELECT @OAIW/app_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOA = (SELECT @OCW*(app_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)

SELECT loginname,
CAST(@TOA AS decimal(18,1)) AS [Opps in Approach],
app_time AS [Approach Average Time],
app_perc_adv AS [Approach Perc Adv],
CAST(@TOI AS decimal(18,1)) AS [Opps in Interview],
int_time AS [Interview Average Time],
int_perc_adv AS [Interview Perc Adv],
CAST(@TOD AS decimal(18,1)) AS [Opps in Demonstrate],
dem_time AS [Demonstrate Average Time],
dem_perc_adv AS [Demonstrate Perc Adv],
CAST(@TON AS decimal(18,1)) AS [Opps in Negotiate],
neg_time AS [Negotiate Average Time],
neg_perc_adv AS [Negotiate Perc Adv],
CAST(@TOC AS decimal(18,1)) AS [Opps In Close],
cls_time AS [Close Average Time],
cls_perc_adv AS [Close Perc Adv]
FROM #pipelinehist
WHERE loginname = @login AND record_date = @record_date

Here is some sample data to use with this. With this sample data what I want to get back is a total of 30 records in the result set each with its data specific to the login and record_date of that returned record.

CREATE TABLE #pipelinehist (
glusftboid int IDENTITY(1,1) NOT NULL,
record_date datetime NOT NULL,
loginname char(20) NOT NULL,
app_new float NOT NULL,
app_time float NOT NULL,
app_perc_adv float NOT NULL,
int_time float NOT NULL,
int_perc_adv float NOT NULL,
dem_time float NOT NULL,
dem_perc_adv float NOT NULL,
neg_time float NOT NULL,
neg_perc_adv float NOT NULL,
cls_time float NOT NULL,
cls_perc_adv float NOT NULL,
target_deal money NOT NULL,
annual_quota money NOT NULL,
weeks int NOT NULL
) ON [PRIMARY]

INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'AREP', 56.8, 26.9, 0.57, 29.5, 0.47, 20, 0.67, 80.7, 0.53, 2.1, 0.97, 2194.93, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'BREP', 33.2, 0.5, 0.9, 7.7, 0.77, 8, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'CREP', 210.2, 0.3, 0.87, 6.6, 0.5, 13.7, 0.4, 16.3, 0.43, 1.5, 0.91, 461.25, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'DREP', 47.6, 5, 0.53, 33.3, 0.6, 57.5, 0.53, 50, 0.7, 1.5, 1, 2045.7, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'EREP', 75.3, 110.9, 0.47, 36, 0.5, 17.4, 0.87, 20.3, 0.6, 7.2, 0.83, 2021.74, 775000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'FREP', 17.2, 23.3, 0.73, 6.8, 0.8, 6.3, 0.93, 29.7, 0.67, 15.5, 0.83, 2218.95, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'GREP', 105.4, 67, 0.2, 32.9, 0.43, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'HREP', 116.4, 118.5, 0.33, 30.9, 0.77, 46.3, 0.77, 46.3, 0.6, 0.9, 0.97, 1735.13, 1150000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'IREP', 143.3, 9, 0.77, 96, 0.17, 21.6, 0.77, 39.9, 0.43, 0.9, 0.93, 1385.43, 400000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 67.6, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'KREP', 107.6, 38.2, 0.23, 47.5, 0.47, 21.3, 0.77, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'LREP', 18.6, 8.3, 0.87, 23.2, 0.57, 2.6, 0.87, 12.2, 0.67, 1, 1, 1229.02, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'MREP', 4, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.4, 1, 1091.22, 350000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'NREP', 54, 21.6, 0.57, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'OREP', 37.6, 24.4, 0.57, 50.1, 0.43, 6.7, 0.87, 15.6, 0.73, 0.9, 0.97, 1163.48, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'AREP', 57.2, 32.5, 0.6, 29.5, 0.47, 20, 0.67, 85.6, 0.5, 2.1, 0.97, 2194.93, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'BREP', 33.9, 0.5, 0.93, 7.8, 0.73, 8.3, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'CREP', 152.1, 0, 0.87, 4.3, 0.67, 9.7, 0.47, 15.7, 0.47, 1.8, 0.85, 396.43, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'DREP', 80.5, 9.8, 0.5, 40.7, 0.57, 68.3, 0.43, 64.2, 0.57, 1.5, 1, 2045.7, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'EREP', 61, 92.1, 0.5, 31, 0.53, 16.9, 0.83, 17.7, 0.6, 7.3, 0.83, 2318.04, 775000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'FREP', 19.4, 21.1, 0.7, 5.3, 0.77, 2.2, 0.93, 33.3, 0.7, 9.7, 0.87, 1937.17, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'GREP', 81.7, 40.5, 0.3, 33, 0.37, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'HREP', 128.6, 115.7, 0.3, 30.9, 0.77, 46.3, 0.77, 48.8, 0.6, 0.9, 0.97, 1728.29, 1150000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'IREP', 100.9, 3.4, 0.77, 86.2, 0.27, 18, 0.8, 54.7, 0.37, 0.9, 0.93, 1385.43, 400000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 63.5, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'KREP', 285.2, 36.5, 0.1, 46, 0.43, 24.2, 0.73, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'LREP', 17.6, 7.3, 0.9, 21.5, 0.57, 1.7, 0.87, 12.2, 0.67, 1, 1, 1250.54, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'MREP', 26.7, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.3, 1, 979.7, 350000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'NREP', 61.6, 20.8, 0.5, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'OREP', 31.6, 16.9, 0.63, 50.1, 0.43, 7.2, 0.87, 19.5, 0.7, 0.9, 0.97, 1303.48, 330000, 50)

View 3 Replies View Related

Getting MAX Value And Displaying Record

Dec 2, 2004

Hi all,

I basically want to display the single row that has the highest 'jobid' using a SP. I was playing with MAX(jobid) but getting errors about no group by, etc. Where do I begin with this?

SELECT Purchord, JobNo, Descr, customer, jobid
FROM Jobs

View 4 Replies View Related

Displaying One Data Record

Jun 30, 2007

Hi all, this is a very basic question of diplaying a data. on my aspx page I have datasource that will return only ONE record.
<asp:SqlDataSource ID="sdsCategoryName" runat="server" ConnectionString="<%$ ConnectionStrings:KaruselaConnectionString %>" SelectCommand="SELECT Title FROM tbh_Categories WHERE CategoryID=@categoryID "> <SelectParameters> <asp:QueryStringParameter DefaultValue="-1" Name="CategoryID" QueryStringField="id" Type="int32"/> </SelectParameters> </asp:SqlDataSource>
on the server side I would like to manipulate the title of the page according to the data returned from the query:
and on the behind code if (!this.IsPostBack && !string.IsNullOrEmpty(this.Request.QueryString["ID"]))
{
DataView dv2 = (DataView)sdsCategoryName.Select(arg);
this.Title = string.Format(this.Title, dv2.Table.Columns[0].ToString());
dv2.Dispose();
}
of course it doesn't work. my question is this. do we really have to put the query datasource on the client side?and secondly, how can I view the recorsd I recieves from the query?Thanks for the help.

View 1 Replies View Related

Displaying Last Record In SQL Database Table

Dec 7, 2005

Got a question here and as I am no expert programmer, this should be easy for you gurus. I have this fairly generic code I've created where I return data from an SQL table in a DataList control. I want to take it to the next level and return only the last record in the table, but I am unsure of how to do that. Perhaps I shouldn't even be using a DataList control, I'm not sure.

View 2 Replies View Related

Displaying No Record Found Message

May 25, 2007

hi all,

i want to show a message when no rows is being returned from the data set,

i.e when there is no data to display the report should display

"No Record Found"

View 3 Replies View Related

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related

Question On Displaying Large Number Of Record?

Oct 20, 2006

I have datagrid that needs to display a log table which has more than million records. Since it it huge number, it is not possible to get dataset using "select * from log_table" to fill and to bind to datagrid.Is there anyway to display first 100 rows on first page and show next 100 rows if use clicks on page 2?Thank you very much in advance!Justin

View 4 Replies View Related

Inserting Records And Displaying Them

Oct 26, 2006

Before I start driving myself nuts, I'd like to make sure my approach is correct.I want to create a simple job posting board.I have a text boxes for company name, email, job title, and job description, and a submit button.I created a table in my database called "JobPostings", with columns called "CoName", "CoEmail", "JobTitle", and "JobDesc"When someone fills out the fields and clicks submit, it will insert the new records.So,1. Is this the correct approach so far?2. What is the best way to display the job listings? A grid view?3. At submit time, how can I include that day's date?4. How can I get the records in the database to delete after 90 days?Thanks.

View 1 Replies View Related

Displaying All Records Of Duplicates

Jul 9, 2007

HI all I have this table below and i need to get records from col1 >1 and col2 >1

col1 col2 language
1 a s
1 b p
3 c e
3 c f

SELECT col1, col2, language COUNT(*) AS Expr1
FROM table1
GROUP BY col1, col2
HAVING (COUNT(*) > 1)

with the above query it gives me this

col1 col2 expr1
3 c 2

the language column is not displayed what i want is not the exact count but all the records which is duplicate and must resemble the result below

col1 col2 expr1 language
3 c 2 e
3 c 2 f

I need the result above inorder to normalize the table and bring out the language part into another table

thanks

View 2 Replies View Related

Sql Query For Displaying Records

Aug 9, 2007

Hi,

I would like to know how would i display following data.

exa:- (Note:- I wrote '---' so to display properly in to the forum

Table1
ComputerName--IpAddress
ABC1-----------10.1.2.3
ABC1-----------10.1.2.4
ABC1-----------10.1.2.5
ABC2-----------10.1.2.6
ABC2-----------10.1.2.7
ABC3-----------10.1.2.8
ABC3-----------10.1.2.9
ABC4-----------10.1.2.10

Table2

ComputerName--NetCard
ABC1-----------<card1>
ABC1-----------<card2>
ABC2-----------<card3>
ABC2-----------<card4>
ABC3-----------<card5>
ABC3-----------<card6>

Table3
ComputerName--Model
ABC1-----------DEll
ABC2-----------DEll
ABC3-----------DEll

Now i want output should be displayed like this

ComputerName--IpAddress--NetCard Model
ABC1-----------10.1.2.3---<card1>---DEll
----------------10.1.2.4---<card2>
----------------10.1.2.5

ABC2-----------10.1.2.6---<card3>---DEll
----------------10.1.2.7---<card4>

ABC3-----------10.1.2.8---<card5>---DEll
----------------10.1.2.9---<card6>

ABC4-----------10.1.2.10

Black filled should not displayed anything

Is it possible

--------------------
Thanks and regards
Abhishek Joshi
India- Bangalore

View 4 Replies View Related

Displaying Records With Recent Date

Jul 12, 2006

I want to display some horse racing results I have on a page but I only want to show the results for the last 7 days.

I have fields for date, time of race, horses name and result.

How can I just get records within the last 7 days to display?

I'm new to this SQL malarky so please be gentle!

View 4 Replies View Related

Counting Parent Records And Displaying Total??

Feb 5, 2004

We have an helpdesk sytem on SQL 2000. I am trying to show how much calls have been assigned to each parent category plus it's child categories in a single row. The thing is when I run my query it display parent and child categories and each on it's own rows. I do get the call totals for each row, but I would like to add the totals of the rows together and display it on row. The new table must have then 6 rows (because there is 6 parent categories) with the total of all calls for that parent category, as well as it's childs. Example:

parameter # Calls
------------ --------
desktoppp 5
desktopqq 6 {This is what I am getting at this stage}
desktop t 4
network
r 9
networkgg 10
softwarevv 3

This is what I would like to have:

parameter # Calls
------------ -------
desktop 15
network 19
software 3

Please Help!

View 2 Replies View Related

T-SQL (SS2K8) :: Displaying Sum Of Records And Show Row Even If It Is Blank Or Zero

Mar 10, 2015

I am creating a report on an asp.net webpage to show how many times a coupon has been used from a set list of items.There are two tables I am using for this query.Table 1 has the list of items or coupons. Table 2 has a column for the item id from Table 1 and a value.

Example:
Table 1
id Name
1 Coupon1
2 Coupon2
3 Coupon3

[code]...

My current query will only show coupon1 and coupon2 and the number and value. It will not show coupon2 since it hasn't been used. I would still like to show coupon2 with a number of zero and a value of zero.

Current query is:
SELECT Table1.Name, COUNT(Table2.ID) AS CNum, SUM(Table2.Value) AS CValue FROM Table2 JOIN Table1 ON Table2.ID = Table1.ID GROUP BY Table1.Name

View 5 Replies View Related

Displaying Records Associated With Records...

Jun 21, 2007

I cannot figure out how to write a sql statement to display the multiple records of multiple records. I know that doesn't make much sense so let me explain:

This problem involves two tables. They both reference (FK) data in a third table (tblDepartment).

Here are the fields and some test data in each of the two important tables:

=============
tblDepartment =============================
departmentIDuserID
-----------------------------
1user1
2user1
1user2
2user3
3user3
3user4

===========
tblRequest ========================================
requestIDdepartmentIDuserID
----------------------------------------
11user1
21user2
32user3
43user3
53user4
62user1

What I am trying to do is display all requests associated with the multiple departments of "user1". In the above example, I would want to see (after a select statement):

requestIDdepartmentIDuserID
----------------------------------------
11user1
21user2
32user3
52user1

We don't see the requests of user3 or user4 when the given request's departmentID is not a department associated with user1.

Hopefully someone out there understands this. I've never had to write a select statement like this before, so I'm having trouble wrapping my mind around it. Thanks for any help!

View 4 Replies View Related

Multiple Record Insertion For Each Record From Source

Jan 10, 2008

Hi,

How can we insert multiple records in a OLEDB destination table for each entry from the source table.
To be more clear, for every record from source we need to insert some 'n' number of records into the destination table. this 'n' changes depending on the record.
how is this achieved.

thanks.

View 4 Replies View Related

Displaying Data From Multiple Rows On 1 Row

Aug 22, 2005

I would like to display data from one column on multiple rows in one row.

Example:

Name Pet
David Dog
Dawn Dog
Dawn Cat
Pete Mouse
Pete Cat
Pete Dog

I would like the result to do the following:

David Dog
Dawn Dog Cat
Pete Dog Cat Mouse

I will not know the row values ahead of time.

View 2 Replies View Related

Displaying Results From Multiple Tables

Apr 17, 2008

Hi,

I have created a new reports application project in VS 2008. I have a dataset with 2 tables: Customer and CustomerAddress with one to many relationship. I want to have a simple table in my report which displays data in the following format:
Customer Name Address
----------------------------------------------------------
ABC Add1
ABC Add2

XYZ Add1
XYZ Add2

Here Address is obviously from CustomerAddress table. I have tried few options but it's mainly disgusting to work with Reports Application project when there was an amazing Busines Intelligence Reports project available in VS 2005 and equally good designer interface (Dataset, Design and Preview tabs for each report).
Please give me a solution to this.

Shyam

View 4 Replies View Related

Displaying Multiple Values In A Concatenated String

Oct 17, 2007

SQL Server 2005.(SP2). MS SSRS;
I want to display some numbers in the same line as a concatenated string. For example a Customer may have multiple bills. These bill numbers are displayed in separate rows. I want to display them all on the same line.
Example of current display:
Customer Bill #
ABC Company 123
ABC Company 456
ABC Company 789 etc

I want this to display as below:
Cusotmer Bill #
ABC Company 123, 456, 789, etc.

Is this possible in SSRS. Please help me with the syntax.

Thanks in advance.

View 2 Replies View Related

Displaying Multiple Charts Using A Single Dataset

Jun 15, 2007

Hello,

I have the following tables :

Area
areano
areaname

Locality

locno
areano

RequestType
reqid
reqdtls

WebSummit

SummitId

RequestorName

DateOfRequest

reqid

Areano

I want to display the total no. of Addition, Removals and Relocations during the past 3 months in a paticular Area.

I have written the query for this.

select q.[areaname] as Area,

classCount as TotalCount, ReqType

from ( select a.areano,

a.classCount, ReqType

from ( select areano,

count(*) as classCount, reqdtls as ReqType

from WebSummit

join RequestType

on WebSummit.reqid = RequestType.reqid

where date_created >= dateadd(mm, -3, getdate())

group by areano, reqdtls

) a

join WebSummit b

on a.areano = b.areano

group by a.areano,

a.classCount, ReqType

) p

join Area q

on p.areano = q.areano

The query works fine and has been tested. Now I want to display this in chart format.

The display should be in a chart format, one for each area showing a bar for each RequestType.

I have placed "TotalCount" in the Data Fields section and ReqType in Category fields. How do I use the Filter to set an expression so that it displays the data in a seperate chart for each Area.

I tried using =Fields!Area.Value and set it to Area1 to display only Area1 values, however the preview shows nothing.

Plz help me out.

Here's some sample data

Area

AreaNo AreaName

1 Area1

2 Area2

3 Area 3

4 Area4

5 Area5

6 Area6

7 Area7

Locality

LocNo AreaNo

1 1

2 1

3 1

1 2

2 2

1 3

2 3

3 3

4 3

1 4

2 4

1 5

2 5

3 5

RequestType

reqid reqdtls

1 Addition

2 Removal

3 Relocate

WebSummit

SummitId RequestorName DateOfRequest reqid AreaNo

1 John 12/6/2007 1 1

2 Jack 13/6/2007 1 1

3 Bill 12/6/2007 2 1

4 Ben 12/6/2007 3 1

5 Dale 14/6/2007 1 2

6 Evjen 15/6/2007 1 2

7 Fuller 16/6/2007 2 2

8 Jimmy 16/6/2007 3 2

9 Kart 16/6/2007 3 2

10 Fuller 16/6/2007 3 2

Regards,

Vidya

View 1 Replies View Related

Displaying Multiple Categories (from Seperate Tables) To Be Viewed On One Page

Mar 24, 2007

Hello.
I just created separate tables for each of my categories and wanted to know how to return them all to be viewed on one page using the SQL Datasource (or whatever) This is for user accounts. I just need to know that part.
Sincerely,
Computergirl

View 1 Replies View Related

Integration Services :: Insert Multiple Columns As Multiple Records In Table Using SSIS?

Aug 10, 2015

Here is my requirement, How to handle using SSIS.

My flatfile will have multiple columns like :

ID key1 key2 key3 key 4

I have SP which accept 3 parameters ID, Key, Date

NOTE: Key is the coulm name from the Excel. So my sp call look like

sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date

View 7 Replies View Related

Selecting Multiple Records By Multiple Filters...

Aug 13, 2006

Hey all,I am having some serious trouble getting the correct syntax for a select statement to work the way I need it, any help I could get on this would be greatly appreciated.I have a table called Units which stores computers and a table called Software which stores software. I have a bit field in Units called OEM, when this is set to true I don't want the select statement to pull this unit down when I am assigning software to other units.Here is my select statement: SELECT Software.SID, Software.SN, Software.Name, Users.First + ' ' + Users.Last AS 'Assigned User', Units.Make + '-' + Units.Model AS 'Assigned Unit' FROM dbo.Software LEFT JOIN dbo.Units ON Software.SN = Units.SN LEFT JOIN dbo.Users ON Units.UID = Users.UID WHERE (Units.OEM = 'FALSE') AND (Software.SN LIKE '%' + @SearchString + '%')Everything works as expected, unless of course the unit has no software assigned to it yet, it won't return it because its not tied to a Units.OEM field. Is there anyway to have it return ALL records that even arn't joined OR are joined but have OEM set to false?Thanks, let me know if I need to clear anything up.-Matthew

View 2 Replies View Related