Combine Authors With Similar IDs

Mar 13, 2014

Im trying this simple query, where if a book has the same ID but different names it should be shown in one row and not two...

Now it is showing

- 1 AuthorDude
- 1 AuthorGirl
- 2 Authorblabla

While it should show

- 1 AuthorDude, AuthorGirl
- 2 Authorblabla

SELECT a.book_id, b.Name
from BOOK_SALES a
left outer join
(
select Book_id,is_primary,
STUFF((select ', ' + Name from BOOK_CONTRIBUTOR B where B.BOOK_ID = A.BOOK_ID
for xml path('')),1,1,'') Name
from BOOK_CONTRIBUTOR A
group by Book_id,IS_PRIMARY
) b on a.BOOK_ID = b.BOOK_ID

When i do a inner join, it would just show many Book_ID`s, but with the authors combined.

[URL] ....

View 3 Replies


ADVERTISEMENT

Gathering Authors In A Stored Procedure

Mar 18, 2014

My stored procedure is now close to finished. The only thing left is that it prints out the "Authors" on several lines. So if a ID has several authors, it will print several lines instead of one. Can i resolve this in a Group By statement, or something else? And where should i put it?

How it is shortly showed(not with all the other stuff):

18.01.2014, Author 1, ItemID1
18.01.2014, Author 3, ItemID1
How it should be:

18.01.2014, Author 1, Author 3, ItemID1

ALTER PROCEDURE [dbo].[LOID] @month INT,
@year INT,
@report_source NVARCHAR(255),
@is_primary INT

[Code]

View 3 Replies View Related

How To Basically Copy Tables With New Names Rather Than Create Similar Tables From Similar Manual Input.

May 26, 2007

I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance. 

View 4 Replies View Related

How To Group Similar Column Name And Sum The Similar Column Name Together

Apr 10, 2008

Hey Gurus,

I have a problem on getting the sql statement which will group similar column name and sum their number together(another column).


A million thanks in advance.

View 5 Replies View Related

Similar To PhpMyAdmin For MS Sql?

Apr 11, 2005

Is there any program similar to phpMyAdmin for MS SQL servers?

Thanks in advance

View 5 Replies View Related

Do I Need DatePart Or Similar

May 11, 2006

I have a function that uses the following statement in it

SELECT src_terrier.Areacode, src_terrier.siteref, src_terrier.estatename, src_terrier.Securitised, src_terrier.unitref, src_terrier.unittype,
src_terrier.unittype_count, src_terrier.tenantname, src_terrier.tenantstatus, src_terrier.tenantstatus_count, src_terrier.unitstatus,
src_terrier.unitstatus_count, src_terrier.floortotal, src_terrier.floortotocc, src_terrier.initialvacarea, src_terrier.initialvacnet, src_terrier.TotalRent,
src_terrier.NetRent, src_terrier.FinalRtLsincSC, src_terrier.ErvTot, src_terrier.tenancyterm, src_terrier.landact, src_terrier.datadate,
src_div_mgr.div_mgr, src_portfolio_mgr.portfolio_mgr, src_centre_list.propcat, src_tbl_rental.budgeted_net_rent,
src_tbl_rental.budgeted_occupancy
FROM src_terrier INNER JOIN
src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN
src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN
src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no LEFT OUTER JOIN
src_tbl_rental ON src_terrier.siteref = src_tbl_rental.site_ref

WHERE (src_terrier.datadate = @dt_src_date) AND
(src_terrier.Areacode = @chr_div) AND
(src_centre_list.Portfolio_no = @vch_portfolio_no) AND
(src_centre_list.propcat = @vch_prop_cat) AND
(src_tbl_rental.site_ref = src_terrier.siteref)

The problem I have is that the 'src_terrier.datadate' is passed through as mm/dd/yyyy (which I do actually want to change to dd/mm/yyyy as that is how the data is stored) however, the src_date within the table src_tbl_rental is only set to 01/mm/yyyy. When I put an inner join on the date element it obviously does not find it as the sample data I am using is as follows

src_terrier = 28/04/2006 and src_tbl_rental is 01/04/2006. Therefore if I pass the same parameter value through the dates are not the same and I get no data at all.

How can I specify that for the purposes of the src_tbl_rental element of the select query, that I only want it to match the mm/yyyy part of the src_date.

Therefore if some passes in
28/04.2006 it will get the records from the terrier table that match that date, and only the records from rental that match the 04/2006 part of the date.

Anybody confused by that , cause I am!

Regards

View 6 Replies View Related

Is There St Similar To Directoryinfo In RS?

Apr 28, 2008

hello there.

is there any case for getting the directory/file structure on the ReportServer?
something similar to "DirectoryInfo".

i'm using the webservice
best would be putting the url (e.g. "http://localhos/reportserver/reportgroup1") to a function and get at least an array of filenames in this folder back.

is there any way to solve this?

thanks in advance, tobias

View 1 Replies View Related

Sys.sql_dependencies Or Similar?

Sep 11, 2007

Hi!
I'm about to write some code that generate TSQLdoc.
For that I would like to show dependencies.

What will the best way to get actual info about
which procedures use this proc
which procedures or functions do this proc or func use?


Thanks

View 2 Replies View Related

Function Similar To ISNULL()

Oct 6, 2005

I'm constructing a single string of several counts with concatenated labels using SQL and want to not show zeros (or their labels). Is there a function within an SQL statement that will let me do this? ISNULL() sort of does this, but I really need to test for zero instead of NULL to eliminate noise data from the string.

View 2 Replies View Related

Similar To Statspack/AWR Report

Dec 13, 2007

Is there anything similar to AWR/statspack report in SQL Server 2005.

View 4 Replies View Related

Difference Between Two Similar Queries

Feb 21, 2008

Hi All,

Is there any difference between the two queries given below..I am not able to find any but am not sure. Kindly help.

A)

select a.* from( select top 1 hs.last_modified, hs.price, hs.revision_date
from history hs where hs.last_modified < '06-Jan-2008' order by hs.last_modified desc)a
order by a.revision_date desc

B)
select hs.last_modified, hs.price, hs.revision_date
from history hs where hs.last_modified < '06-Jan-2008'
order by hs.last_modified desc, hs.revision_date desc




Thanks,
Pradeep.D

View 4 Replies View Related

Using Somthing Similar Than Squence In Ms Sql

Feb 6, 2007

Hi,
I need something like a sequence in a datafield of my table. (unique number in the table) is a uniqueidentifier the right thing to choose? and if yes, how to I insert a new value into the that table:

a) from the SQL Enterprise Manager?
b) from a JAVA program using jdbc?

thanks for all hints.

View 2 Replies View Related

Similar To COALESCE Function

Nov 4, 2004

I have three fields in a table say [F1, F2 & F3]. I need to fetch anyone of these three fields which has the maximum value between them.

In Simple words i'm looking for some function which is similar to COALESCE function which returns the first NOT NULL value of the fields that were passed as arguments.

FYI I'm using SQL Server 7.0 which does not supports UDF's

Earlier response appreciated

Thanks and Regards
Chandru

View 3 Replies View Related

Add Similar Fields To Many Tables

Dec 13, 2004

Folks, i have to create four fields in every user table within my database:

CREATED_BY VARCHAR(25), CREATED_DATE [DATETIME], MODIFIED_BY VARCHAR(25), MODIFIED_DATE [DATETIME]

There are more than hundred tables, so i wanna automate this. i am tryin to do this in a cursor: please guide!

declare @name VARCHAR (50)
declare cur cursor
fast_forward
for select name from sysobjects where type='u' and status not like '-%'
open cur
WHILE (1=1)
BEGIN
FETCH NEXT
FROM cur
INTO @name
IF @@fetch_status = 0
BEGIN
ALTER TABLE @name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD created_date [DATETIME]
GO
ALTER TABLE @name
ADD modified_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD modified_date [DATETIME]
END
ELSE
BREAK
END
DEALLOCATE cur


I also want that if one column for a table exists; the other columns should be created rather than it quits.


Howdy!

View 4 Replies View Related

Select Similar Data

Aug 15, 2012

finding is queries to find duplicate data.Basically I have a picklist table in a database and I have discovered that there are what looks like duplicate data (because the name is the same) but there is a different number on the end, as you can see from an example below.

NO, ENTRY
24, John Doe|26|05768
24, John Doe|26|5768

Do you know if there is a sql query that can be ran against this table that will look through the ENTRY column and select fields that are similar and not duplicates (duplicates can't exist due to PK constraints)

View 8 Replies View Related

Merge And Sum Similar Records

Nov 20, 2012

I have the below in a table - TABLEA

Ref, Date, TIME, Code, Minutes
01117,2012-01-02, 541,BASIC,240.0
01117,2012-01-02, 541,BASIC,105.0

And I am trying to insert this into another table TABLEB but it wont allow as I am getting a duplicates error because of the unique Indexing on the table.

Ref,Date,Time,Code

Ideally if I could run a query on TABLEA so that it would merge and sum the minutes where REF,Date,TIME,CODE are the same.

i.e., the above would become

Ref , Date , TIME, Code, Minutes
01117,2012-01-02, 541,BASIC,345.0

Is this possible?

Another option that would work for me is the TIME column info isnt required to remain at 541.

If there was a count increment on the rows it would allow the import to rum.

ie if the above became

Ref, Date, TIME, Code, Minutes
01117,2012-01-02, 1,BASIC,240.0
01117,2012-01-02 ,2BASIC,105.0

It would also import correctly.

The first option is which I would prefer.

View 2 Replies View Related

Join On Similar Data

Feb 23, 2015

Table One is an older database and has the column employee id, which would always consist of first letter of the last name an underscore and a numeric value. So for example, data looks like

employeeID
R_12345678
S_5555555
T_777777
U_7777

Our new data structure simply removed the letter & underscore.

employeeID
12345678
5555555
777777
7777

Now my question is, how could I join on oldDB.employeeID to newDB.employeeID since the data is very similar, but not exactly the same?

View 9 Replies View Related

Find Out Similar Columns

Apr 19, 2007

i like to write a sql query to find out similar columns in different tables.

i have something like this. but my logic is wrong.

select *
from information_schema.columns a
join information_schema.columns b
on a.column_name = b.column_name
and a.table_name <> b.table_name

View 1 Replies View Related

How To Go About Adding Similar Tables?

May 26, 2007

I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.

Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.

I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.

I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.

How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?

Thanks in advance.

View 5 Replies View Related

Select Command Done Similar To Insert?

Aug 2, 2007

I did a successful Insert to a SQL Server today in .NET VS2005.
But before I insert I figure I better make sure the part does not allready exist in the table.
Can a check the command to determine if it has a boolan value of False and if so to INSERT as before?  Dim myCommand As New SqlClient.SqlCommand

myCommand.CommandText = "Select PartNumber From Pricing Where PartNumber = '" & var0 & "'"

'myCommand.CommandText = "INSERT INTO Pricing (PartNumber, ListPrice, PartDescription, ProductClassCode, ProductClassDescription, ProductFamilyCode, ProductFamilyDescription, ProductLineCode, ProductLineDescription) VALUES('" & var0 & "', " & var1 & ", '" & var2 & "', '" & var3 & "', '" & var4 & "', " & var5 & ", '" & var6 & "', '" & var7 & "', '" & var8 & "')"

myCommand.Connection = con


con.Open()
myCommand.ExecuteNonQuery()
con.Close() 
 

View 4 Replies View Related

Array (or Similar Thing ) In Sql Server

Jul 4, 2005

I would like to write a fun or stored procedure to do some operation. It require me to know that what category is currently belong to certain people(people_table: category_table1               to           Many)However, when i use the select statement in stored proc, it return a set of result, not a scalar , therefore, i cannot use the variable to hold it. In addition, there are no array in SQL server.Question:1. Is there any way to hold the collection of result(like array)?2. Also, how to determine to use fun or stored procedure?(Since a integer is need to return by them)Thx

View 4 Replies View Related

COMMAND SIMILAR TO SPOOL IN ORACLE

Jan 16, 2002

Hi all,
I have a table with the list of tables I need to drop. So basically before droping those tables I need to disable the FK and PK constraints.
So I want to spool out the out of this script.
SELECT 'ALTER TABLE ' +
QUOTENAME( c.TABLE_NAME ) +
' NOCHECK CONSTRAINT ' +
QUOTENAME( c.CONSTRAINT_NAME ) AS ALTER_SCRIPT
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

Is there a way to store the output of this script in a .sql file so that I could execute it.
Any thoughts will help!
Thank you!

View 5 Replies View Related

Does Anyone Know Of A Great Access Help Site Similar To This.

May 17, 2000

Does any one have information on any good Access Sites that are similar to this one, which could be helpful in completing some task. Thanks.

View 1 Replies View Related

Mssql Query Similar To Unix_timestamp

Jan 18, 2007

Hi

I have a mysql query in my php script like
UNIX_TIMESTAMP() - UNIX_TIMESTAMP(sessioncreated) as sessionspan .
What is the equivalent of above query in mssql. I need the same query in mssql. Is there any function that does the same action in mssql2000.

Thanks in Advance.
stranger

View 3 Replies View Related

How Can I Apply Transaction Log To The Other Similar SQL Server

Apr 26, 2001

I have two SQL 6.5 servers running independently. Both servers have almost identical databases. For example, we create purchase order in SQL server A. And post the same purchase order # for it's PO Receipt and PO Inspection in SQL server B. How can I apply all transactions entered from SQL server A to SQL server B, and vise versa at end of the day. How can I get help or training for writing codes for manipulating transaction log?

View 1 Replies View Related

Function To Match Similar Words

Mar 30, 2007

Is there a function to match simalar words
say for example 'tom' will match 'tomy'

Thanks

View 4 Replies View Related

A Tool Similar To SQL Impact From Quest?

Oct 4, 2006

Hi,
I am looking for a tool that is similar to SQL Impact (Quest). Quest has discontinued the tool.

This tool should be able to detect all database object dependencies for SQL Server, Sybase and Oracle. The objects should include tables, views, stored procedures, indexes and other objects. This should also detect DB object dependencies in front end applications as well.

Any suggestions are greatly appreciated...

Thanks!

View 2 Replies View Related

Normalize A Table With 3 Similar Columns (was Plz Help Out)

Oct 30, 2006

Hi,I am working on SQL Server.
I have a table with columns that need to be broken down. I nees to break this table:

TelNo1 TelNo2 TelNo3

555-44-33 555-43-88 555-46-89

into:


Tel_Number

555-44-33
555-43-88
555-46-89

I tried using the union statement but i just did'nt succed. Can anyone help me,please

View 1 Replies View Related

Checking Columns In Two Similar Tables

Jan 3, 2014

For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.

There is only one column extra in _dup tables i.e.,idn column.

Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.

View 6 Replies View Related

Comparing 2 Similar Columns In Different Tables

Mar 5, 2014

I am trying to write an SQL command for my crystal report. I need to compare the same column in 3different tables & get the data from each table for only the matching data.. I understand I need to create a temporary table, get the data into it & then work around.. I am quite new to SQL.

Eg: Considering one customer account
Table 1
Cust.No Name Amt_Counter AmtPaid
123.456 sam 0 0
123.456 sam 1 50

Table 2
Cust.No Name Freq_Counter Frequency
123.456 sam 1 0
123.456 sam 2 15

[code]....

View 3 Replies View Related

Update Query For Similar Values

Nov 8, 2013

I have a grid like this and when I change Designation value of Name X from PA to PAT, this change should reflect to Sathish Designation too. Changing similar values should reflect all over the grid. Like update database on change of value of similar kind.

By far I use this query to update the grid. So a where clause should be used now to update the grid to fit this scenario.

("UPDATE AppInvent_Test SET Name = @Name, Designation = @Designation, City = @City WHERE EmpID = @EmpID");

EmpID Name Designation City
21 X PA Chn
2 Sathish PA Chn
3 Shiva A Cbe
17 Venkat M Hyd
22 Y SM Cbe
18 Vignesh SA Hyd

Table:
CREATE TABLE [dbo].[AppInvent_Test](
[EmpID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL,
[Designation] [varchar](100) NULL,

[Code] ....

View 4 Replies View Related

Fetch Similar Columns From Two Tables?

Dec 10, 2013

I am using this query to search checkbox values.

SqlCommand cmd = new SqlCommand("Select * from Details where Emp_Code in (" + selectedValues + ")", con);

I want to join a table called Materials to this now. Material table also has an Emp_Code column. How can I write a select sql query to fetch Emp_Code from both Details and Materials table.

SqlCommand cmd = new SqlCommand("select D.Emp_Code, M.Emp_Code from Details D, Materials M where D.Emp_Code = M.Emp_Code in (" + selectedValues + ")", con);

View 2 Replies View Related

Checking Columns In Two Similar Tables

Jan 3, 2014

For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.

There is only one column extra in _dup tables i.e.,idn column.

Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.

View 5 Replies View Related







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