Removing Rows With Repeated Data

Sep 7, 2005

I am running a query on multiple tables and the data I get back consists of several repeated rows but with one column different. I want to take out those repeated rows and for the column that is different join that data and separate it by a comma. Can this be done?

Ex.
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 717
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 610
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 310

So i would like this data to come up as:
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 717,610,310

View 7 Replies

Comparing Similar Tables - Removing Duplicate Or Repeated Data

Sep 30, 2007

It seems that there should be a solution for my situation, but for the life of me I can't seem to figure it out.

I need to compare two "like" tables, containing similar data. Tbl 1 is "BOOKED" (which is a snapshot of inventory) and tbl 2 is "CURRENT" (the live - working inventory table). If I write my query as follows the the subsequent result is "duplicate" data.

Code Block
SELECT booked.item, booked.bin, booked.quantity, current.bin, current.quantity
FROM BOOKED
LEFT JOIN
CURRENT
ON booked.item = current.item

No matter what type of join I use, there is duplicate data displayed for each table. For example, if there are more bins in the BOOKED table that contain a certain product then the CURRENT table will repeat data and vica versa.

As follows:

Item
Bin
Quantity
Bin
Quantity

12345
A01
500
A01
7680

12345
B01
6
A01
7680

12345
C01
20
A01
7680

54321
G10
1032
E15
1163

54321
G10
1032
F20
523

54321
G10
1032
H30
750

98765
Z20
7000
Z20
8500

98765
Y15
2500
Y15
3000

98765
X10
1200
Y15
3000

What I would like to do is display Bin and Quantity only once and the repeating values as NULL or [BLANK]. Or, to display all of the bins from both tables and only the quantities from each table in relation to the bin found in that table, returning a "0" if no quantity exists.

This is what I'm after:

Item
Bin
Quantity
Bin
Quantity

12345
A01
500
A01
7680

12345
B01
6
B01
0

12345
C01
20
C01
0

54321
G10
1032
E15
1163

54321
F20
0
F20
523

54321
H30
0
H30
750

98765
Z20
7000
Z20
8500

98765
Y15
2500
Y15
3000

98765
X10
1200
X10
0

Is this possible? If so, how?

I also might add that it is ok for each table to contain multiple entries for any given item. This is basically being requested as an inventory variance report - inventory before physical count and immediatly after physical count - and will only be run once a year.

-----------------------------------------------
Just thinking out loud here:
What if I created three subqueries, the first containing only BOOKED information, the second containing only CURRENT information and the third being a UNION of both tables? Something like this:

Code Block
SELECT q3.bin, q1.item, ISNULL(q1.quantity, 0) as QTY_BEFORE, ISNULL(q2.quantity, 0) as QTY_AFTER

FROM

(select item, bin, quantity
from BOOKED)q1
Left Join

(select item, bin, quantity
from CURRENT)q2
on q1.item = q2.item
Left Join

(select bin, item
from BOOKED
UNION
CURRENT)q3
on q1.item = q3.item

Order By q1.item

I don't know if I wrote the UNION statement correctly, but I will have to try this when I get back to work...

Any suggestions?

View 7 Replies View Related

Rows Repeated With Same Data

Dec 17, 2013

For the below mentioned query their is repetition of rows with the same data.

SELECT srs.prod_area as PA,
srs.art as ArtNo,
b.adsc_unicode as ArtName,
cast(case when a.unit <> '2' then c.avsx/10 else c.avsx/1000 end as integer(2)) as AwsMHS,
cast(srs.estimate/10 as integer(1)) as AwsSRSThisWeek,

[Code] ....

View 4 Replies View Related

SELECT The Repeated-same Rows

Feb 5, 2008

Hi!

I am beginner on using SQL.
How can I select the repeated rows (five or more times) on a table which have the same ID's but different updated date. I do not need to group all the rows with the same ID,s but rows which are repeated many times according to the required reports needed.

Below are some information regarding tables and views:

VIEW name= dbo.tblCCInvoicesH
COLUMN ID= ConsumerID
COLUMN DATE= ReadingDate

Thank you in advance...

meti

View 1 Replies View Related

Tsql - Avoid Repeated Rows

Sep 23, 2007

Hi Guys,

I am using the query below to retrieve these results:
You can see that the results are repeated, once for DATIF = 1 and then again for DATIF = 2.
In this case does not matter if the results appear close to DATIF 1 or DATIF 2.
Take in care that I can not know how may extradates or Extrasums are attached to each Account.

Is there any way to avoid these repeated rows?
Thanks in advance,
Aldo.

ACCOUNTKEY DATFID DATFNAME DATF SUFID SUFNAME SUF

--------------- ----------- -------------------------------------------------- ----------------------- ----------- -------------------------------------------------- ----------------------

123456 1 ExtraDates01 2005-01-01 00:00:00.000 1 ExtraSum01 4

123456 1 ExtraDates01 2005-01-01 00:00:00.000 2 ExtraSum02 3

123456 1 ExtraDates01 2005-01-01 00:00:00.000 3 ExtraSum03 1

123456 1 ExtraDates01 2005-01-01 00:00:00.000 4 ExtraSum04 2

123456 2 ExtraDates02 2004-01-01 00:00:00.000 1 ExtraSum01 4

123456 2 ExtraDates02 2004-01-01 00:00:00.000 2 ExtraSum02 3

123456 2 ExtraDates02 2004-01-01 00:00:00.000 3 ExtraSum03 1

123456 2 ExtraDates02 2004-01-01 00:00:00.000 4 ExtraSum04 2

Code Snippet
SELECT DISTINCT

Accounts.ACCOUNTKEY,

ExtraDates.DATFID,

ExtraDateNames.DATFNAME ,

ExtraDates.DATF ,

ExtraSums.SUFID ,

ExtraSumNames.SUFNAME ,

ExtraSums.SUF
FROM
EXTRADATES AS ExtraDates
LEFT OUTER JOIN EXTRADATENAMES AS ExtraDateNames ON ExtraDates.DATFID = ExtraDateNames.DATFID
RIGHT OUTER JOIN ACCOUNTS AS Accounts ON ExtraDates.KEF = Accounts.ACCOUNTKEY
LEFT OUTER JOIN EXTRASUMS AS ExtraSums
LEFT OUTER JOIN EXTRASUMNAMES AS ExtraSumNames ON ExtraSums.SUFID = ExtraSumNames.SUFID ON Accounts.ACCOUNTKEY = ExtraSums.KEF
LEFT OUTER JOIN EXTRANOTENAMES
RIGHT OUTER JOIN EXTRANOTES ON EXTRANOTENAMES.NOTEID = EXTRANOTES.NOTEID ON Accounts.ACCOUNTKEY = EXTRANOTES.KEF
WHERE
Accounts.SORTGROUP BETWEEN 0 AND 999999999
AND Accounts.ACCOUNTKEY BETWEEN '123456' AND '123456'

View 2 Replies View Related

Search Repeated Record And Give Suggestion As Most Repeated One

Sep 4, 2007

I have problem, i wanted a query which will search the duplicate and then give suggestionmost repeated word
Table containing the records like below

ID
Movie Name
New Name

1
Spider Man
Spider Man

2
Spider Man 2
Spider Man

3
Spider Man 3
Spider Man

4
Spider Man UK
Spider Man

5
Spider Man USA
Spider Man

6
New Spider Man
Spider Man

7
Spider Man Black
Spider Man

8
Spider Man Part 1
Spider Man

9
Spider Man Part 2
Spider Man

10
Spider Man I
Spider Man

11
Spider Man III
Spider Man

12
Spider Man Part II
Spider Man
My manufacturer send me the data in this format and i have to allocate there new name
to do some comparison
I wanted to make this process automatic.
what i mean is i need a query which will give me a repeated records along with suggestion
as new name.
I am fully confident that you guys will help me out from this problem.
Looking forward

View 9 Replies View Related

Repeated Data

Dec 21, 2004

If I have a student table with 3 columns ID, FirstName and LastName
ID- FirstName -LastName
-------------------------
1 Tom Hanks
2 Jerry Thomas

and I have a subject table with two columns StudentID and Subject. StudentID is a foreign key to the student table.
StudentID - Subject
------------------------
1 History
1 Biology
2 History

If my query is
select distinct student.id, student.fname, student.lname, student.subject from student
left outer join subject on student.id = subject.studentid

then I get
1 Tom Hanks History
1 Tom Hanks Biology.

Is there a way I could get
1 Tom Hanks History
null null null Biology

--> meaning I don't want to repeat data??

thanks
Teena

View 1 Replies View Related

Need To Avoid Repeated Data In A DataGrid

Apr 15, 2004

Hi there :)

I am developing a system for my uni course and I am stuck a little problem...

Basically its all about lecturers, students modules etc - A student has many modules, a module has manu students, a lecturer has many modules and a module has many lecturers.

I am trying to get a list of lecturers that run modules associated with a particular student. I am able to get a list of the appropriate lecturers, but some lecturers are repeated because they teach more than one module that the student is associated with.

How can I stop the repeats?

Heres my sql select code in my cs file:

string sqlDisplayLec = "SELECT * FROM student_module sm, lecturer_module lm, users u WHERE sm.user_id=" + myUserid + "" + " AND lm.module_id = sm.module_id " + " AND u.user_id = lm.user_id ";
SqlCommand sqlc2 = new SqlCommand(sqlDisplayLec,sqlConnection);
sqlConnection.Open();
lecturersDG.DataSource = sqlc2.ExecuteReader(CommandBehavior.CloseConnection);
lecturersDG.DataBind();

And here is a pic of my Data Model:
Data Model Screenshot

Any ideas? Many thanks :) !

View 1 Replies View Related

Matrix Show Repeated Data

Jan 12, 2007

Hi,

i have a matrix with some fixed columns. when the fixed columns have repeated data, the repeated data is hidden.

how do you show the data all the time??

eg. i have:

Customer - Month - bikes - balls - toys - Total

Sam - January - 0 - 4 - 10 - 14

- February - 3 - 2 - 8 - 13

- March - 2 - 0 - 9 - 11

Tom - January - 2 - 5 - 2 - 9

- February - 5 - 2 - 8 - 15

- March - 1 - 6 - 3 - 10

but i want it to show like this:

Customer - Month - bikes - balls - toys - Total
Sam - January - 0 - 4 - 10 - 14
Sam - February - 3 - 2 - 8 - 13
Sam - March - 2 - 0 - 9 - 11
Tom - January - 2 - 5 - 2 - 9
Tom - February - 5 - 2 - 8 - 15
Tom - March - 1 - 6 - 3 - 10

Thanks.

View 3 Replies View Related

Normalization - How To Get Repeated Data From Existing Table

Jul 8, 2014

View 3 Replies View Related

HELP!!! Creating Nested Data In Repeated Region Problem

Jul 28, 2007

Hi All, Would appreciate some assistance with the attached, I'm using some fairly complicated recordsets (ASP VBScript) for a property search website, I have a page where a user selects search by County this in turn takes them to a page which lists all the towns in the county and number of live properties in each, I'm using 4 recordsets for this - 3 of which list the towns (townsA2I, townsJ2R & townsS2Z) the 4th is a Property Count, examples of the recordsets are below (using townsAtoI and LiveProperties -

TownsA2I

<%
Dim TownA2I
Dim TownA2I_numRows

Set TownA2I = Server.CreateObject("ADODB.Recordset")
TownA2I.ActiveConnection = MM_recruta2_STRING
TownA2I.Source = "SELECT towncountyID, Town, County FROM dbo.easytolettowncounty WHERE Town LIKE 'A%' AND County = '" + Replace(TownA2I__MMColParam, "'", "''") + "' OR Town LIKE 'B%' AND County = '" + Replace(TownA2I__MMColParam1, "'", "''") + "' OR Town LIKE 'C%' AND County = '" + Replace(TownA2I__MMColParam2, "'", "''") + "' OR Town LIKE 'D%' AND County = '" + Replace(TownA2I__MMColParam3, "'", "''") + "' OR Town LIKE 'E%' AND County = '" + Replace(TownA2I__MMColParam4, "'", "''") + "' OR Town LIKE 'F%' AND County = '" + Replace(TownA2I__MMColParam5, "'", "''") + "' OR Town LIKE 'G%' AND County = '" + Replace(TownA2I__MMColParam6, "'", "''") + "' OR Town LIKE 'H%' AND County = '" + Replace(TownA2I__MMColParam7, "'", "''") + "' OR Town LIKE 'I%' AND County = '" + Replace(TownA2I__MMColParam8, "'", "''") + "' ORDER BY Town ASC"
TownA2I.CursorType = 0
TownA2I.CursorLocation = 2
TownA2I.LockType = 1
TownA2I.Open()

TownA2I_numRows = 0
%>

LiveProperties

<%
Dim LiveProperties
Dim LiveProperties_numRows

Sub sLivePropertyCount(vLocation)
vCount=0

Set LiveProperties = Server.CreateObject("ADODB.Recordset")
LiveProperties.ActiveConnection = MM_recruta2_STRING
LiveProperties.Source = "SELECT COUNT(PropertyID) As NumberofProperties, propertylive, propertylocation FROM dbo.easytoletproperty WHERE propertylive = 'y' AND propertylocation = " & vLocation & " GROUP BY propertylocation, propertylive"
LiveProperties.CursorType = 0
LiveProperties.CursorLocation = 2
LiveProperties.LockType = 1
LiveProperties.Open()

vCount=(LiveProperties("NumberofProperties"))
Response.write(vCount)

LiveProperties.Close()
Set LiveProperties = Nothing
end sub

LiveProperties_numRows = 0
%>

I've tried following the tutorial here http://www.webthang.co.uk/tuts/tuts_dmx/rob9/rob9.asp but when i review the page i get the following error -

"Microsoft OLE DB Provider for SQL Server error '80040e14'

Invalid column name 'Barnet'.

/PropertiesbyTown1.asp, line 338 "

Where line 338"

Where line 338 is the "LiveProperties.Open()" of the above recordset, when i try to view bindings on this recordset Dreamweaver gives me the following errors -

"Column 'dbo.easytoletproperty.propertylive' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Column 'dbo.easytoletproperty.propertylocation' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause."

I would appreciate any assistance -

View 1 Replies View Related

Removing Duplicate Rows

Dec 3, 2005

Hi,

Please give the DML to SELECT the rows avoiding the duplicate rows. Since there is a text column in the table, I couldn't use aggregate function, group by (OR) DISTINCT for processing.

Table :

create table test(col1 int, col2 text)
go
insert into test values(1, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(4, 'dbc')
go

Please advise,

Thanks,
MiraJ

View 7 Replies View Related

Removing Rows When A Textbox Value Is 0

Feb 2, 2008

Hi,

I am trying to remove rows of data when the value in a textbox in the row is 0.

My expression for a value of a textbox in my report layout is:

=Fields!salary_FTE.Value+Fields!Leave_FTE.Value

Could you please show me how I edit this expression so a row of data does not show when the value in this textbox is 0, or, should I create this filter in my query rather than in the report layout?

Thanks

View 5 Replies View Related

Removing Repetetive Rows

May 12, 2008

Hi, everyone
This is my data comming from Long StroredProcedure.In this Procedure will have 5 CTE's. I want to do with other function. i.e Removing Repetetive data form ACTION column. This ACTION column consist with BUY/SELL. Whenever two or More BUY/SELL i want to remove that one.

INPUT:
TABLE1
TIME ACTION
2008-05-09 19:33:07.657 SELL
2008-05-09 19:33:08.017 SELL
2008-05-09 19:33:08.360 BUY
2008-05-09 19:33:08.703 SELL
2008-05-09 19:33:09.063 SELL
2008-05-09 19:33:09.423 BUY
2008-05-09 19:33:10.093 BUY
2008-05-09 19:33:10.437 SELL
2008-05-09 19:33:10.797 SELL

OUTPUT:

TIME ACTION
2008-05-09 19:33:07.657 SELL

2008-05-09 19:33:08.360 BUY
2008-05-09 19:33:08.703 SELL

2008-05-09 19:33:09.423 BUY

2008-05-09 19:33:10.437 SELL

Please Help ME anyone..

View 6 Replies View Related

SQL: Removing Partial Duplicate Rows

Dec 24, 2007

Hi,
I have results from a survey in a table, every entry is assigned a unique ID. I want to remove duplicate entries based on the survey data but not on the unique ID (obviously).
So far I have...SELECT DISTINCT RespondantID, Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10, Q11, Q12, Q13
FROM Results
But that gives...

1 - Anonymous
1
1
1
1
1
1
1
1
1
1
1
1
1

2 - Anonymous
2
2
2
2
2
2
2
2
2
2
2
2
2

3 - Martin
2
2
2
2
2
2
2
2
2
2
2
2
2
I.e. in the above example, it would seem that 'Martin' submitted his data once and then submitted it again with his name.
How can I remove the '2 - Anonymous' frrom the data set?
Thanks!

View 8 Replies View Related

Removing Rows That Contains Long String

Mar 23, 2004

Anyone know how to check the length of a varchar data in a table? I want to delete rows that contains an entry that exceeds 100 characters.

I should be able to write a program to do it, but I am wondering if I can do it in SQL.

View 2 Replies View Related

Removing Duplicate Rows In Grouping

Feb 14, 2012

i've to generate a notepad using this query in vb.net :

strSql = "Select count(*), d.ShareholderId, d.UsufructId, d.BnkAccount, b.SBMCode, " & _
"LTRIM(ISNULL(d.TitleCode + ' ', '')) + LTRIM(ISNULL(d.Forename + ' ', '')) + d.Surname as ShName," & _
"d.BankCode, (select count(*) from (select ShareholderId from dividend " & _
"where CompCode = 'L1' and PaymentMode = 'B' group by ShareholderId, UsufructId, " & _

[Code]....

In the select statement i need to select "d.amount" as well. When i do so, it ask me to insert it in the group by option or in an aggregate function.

Grouping by "d.amount" returns extra field as there can be 2 similar "d.shareholderId" but with different amount.

how to group the "d.amount" without having repetition in the "d.shareholderid" ??

View 5 Replies View Related

Removing Columns And/or Rows On Export

Apr 3, 2007

I have a Report I need to hide subtotals and total and include detail only when I am exporting to excel. So, is there a way to capture that it is exporting to Excel? Possibly a way to capture the rs:Format=Excel?

View 5 Replies View Related

Sort Wrongly Removing Rows

Aug 7, 2007

I have a sort component which is (wrongly) reducing the number of rows* in my dataset.

NB "remove duplicates" is NOT ticked!

Why is this occuring?

*number displayed on the flow arrows at runtime

View 19 Replies View Related

Removing Rows From A SqlDataSource Before It Is Bound To A GridView?

Nov 5, 2006

I have a SqlDataSource that I need to remove the first 3 rows from before it is bound to a GridView. How would I go about doing this?
(if I could remove them at the db level in the sproc I would, but right now that is not an option - so I need to do it once I've already received the data)
Thanks.

View 9 Replies View Related

Transact SQL :: Removing Duplicates Rows With OVER Clause

Jun 2, 2015

I have an existing stored table with duplicate rows that I want to delete.Using a cte gives me

WITH CTE AS
(
SELECT rn = ROW_NUMBER()
OVER(
PARTITION BY employeeid, dateofincident, typeid, description
ORDER BY Id ASC), *
FROM dbo.TableName
)
DELETE FROM cte
WHERE rn > 1

This is what I want to do basically. But this is only deleting in my CTE, is there anyway I can update my existing table "TableName" with this, without using temp tables?

View 4 Replies View Related

Removing Partially Duplicate Rows For Resultset ? Help Needed.

Feb 2, 2008

Hi, Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. Please provide separate solutions for SqlServer2000/2005.
I have four tables namely – Forums,Topics,Threads and Messages in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :-
SELECT Forums.forumid AS ForumID,Topics.topicid AS TopicID,Topics.name AS TopicName,LastPosts.date as LastPostDate,LastPosts.author AS Author,
(select count(threadid) from Threads where Threads.topicid=Topics.topicid) as NoOfThreads
FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid
LEFT OUTER JOIN
(
                 SELECT Topics.forumid,Threads.topicid,Messages.date,Messages.author FROM Topics
             INNER JOIN Threads ON Topics.topicid = Threads.topicid
                 INNER JOIN Messages ON Threads.threadid=Messages.threadid
             WHERE Messages.date=(SELECT MAX(date) FROM Messages WHERE Messages.threadid = Threads.threadid)
) as LastPosts
ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid
Whose result set is as below:-

forumid
topicid
name
LastPostDate
author
NoOfThreads

1
1
Java Overall
2008-02-02 13:06:06.267
l@m.com
2

1
1
Java Overall
2008-01-27 14:46:41.000
c@b.com
2

1
2
JSP
NULL
NULL
0

1
3
EJB
NULL
NULL
0

1
4
Swings
2008-01-27 15:12:51.000
p@q.com
1

1
5
AWT
NULL
NULL
0

1
6
Web Services
NULL
NULL
0

1
7
JMS
NULL
NULL
0

1
8
XML,HTML
NULL
NULL
0

1
9
Javascript
NULL
NULL
0

2
10
Oracle
NULL
NULL
0

2
11
Sql Server
NULL
NULL
0

2
12
MySQL
NULL
NULL
0

3
13
CSS
NULL
NULL
0

3
14
FLASH/DHTLML
NULL
NULL
0

4
15
Best Practices
NULL
NULL
0

4
16
Longue
NULL
NULL
0

5
17
General
NULL
NULL
0
I want the result set as follows:-

forumid
topicid
name
LastPostDate
author
NoOfThreads

1
1
Java Overall
2008-02-02 13:06:06.267
l@m.com
2

1
2
JSP
NULL
NULL
0

1
3
EJB
NULL
NULL
0

1
4
Swings
2008-01-27 15:12:51.000
p@q.com
1

1
5
AWT
NULL
NULL
0

1
6
Web Services
NULL
NULL
0

1
7
JMS
NULL
NULL
0

1
8
XML,HTML
NULL
NULL
0

1
9
Javascript
NULL
NULL
0

2
10
Oracle
NULL
NULL
0

2
11
Sql Server
NULL
NULL
0

2
12
MySQL
NULL
NULL
0

3
13
CSS
NULL
NULL
0

3
14
FLASH/DHTLML
NULL
NULL
0

4
15
Best Practices
NULL
NULL
0

4
16
Longue
NULL
NULL
0

5
17
General
NULL
NULL
0  I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the message) from the Messages table as shown above.
When I use the query by using only three tables namely Forums,Topics and Threads, I get the correct result. The query is as:-
SELECT Topics.forumid AS ForumID,Forums.name AS ForumName,Topics.topicid as TopicID,Topics.name as TopicName,
LastPosts.author AS Author,LastPosts.lastpostdate AS LastPost,
(select count(threadid) from threads where Threads.topicid=Topics.topicid) as NoOfThreads
FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid LEFT OUTER JOIN (
             SELECT Topics.forumid, Threads.topicid, Threads.lastpostdate, Threads.author FROM Threads
             INNER JOIN Topics ON Topics.topicid = Threads.topicid
             WHERE Threads.lastpostdate IN (SELECT MAX(lastpostdate) FROM threads WHERE Topics.topicid = Threads.topicid)
             ) AS LastPosts
             ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid
Whose result set is as:-

ForumID
ForumName
TopicID
TopicName
Author
LastPost
NoOfThreads

1
Developers
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
Developers
2
JSP
NULL
NULL
0

1
Developers
3
EJB
NULL
NULL
0

1
Developers
4
Swings
p@q.com
2008-01-27 15:12:51.000
1

1
Developers
5
AWT
NULL
NULL
0

1
Developers
6
Web Services
NULL
NULL
0

1
Developers
7
JMS
NULL
NULL
0

1
Developers
8
XML,HTML
NULL
NULL
0

1
Developers
9
Javascript
NULL
NULL
0

2
Database
10
Oracle
NULL
NULL
0

2
Database
11
Sql Server
NULL
NULL
0

2
Database
12
MySQL
NULL
NULL
0

3
Desginers
13
CSS
NULL
NULL
0

3
Desginers
14
FLASH/DHTLML
NULL
NULL
0

4
Architects
15
Best Practices
NULL
NULL
0

4
Architects
16
Longue
NULL
NULL
0

5
General
17
General
NULL
NULL
0
The scripts for creating the tables and inserting test data is as follows in an already created database:-
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__35BCFE0A]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__35BCFE0A
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__34C8D9D1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__34C8D9D1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Messages__thread__33D4B598]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Messages] DROP CONSTRAINT FK__Messages__thread__33D4B598
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Forums]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Topics]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Threads]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Messages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Messages]
GO
CREATE TABLE [dbo].[Forums] (
            [forumid] [int] IDENTITY (1, 1) NOT NULL ,
            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topics] (
            [topicid] [int] IDENTITY (1, 1) NOT NULL ,
            [forumid] [int] NULL ,
            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Threads] (
            [threadid] [int] IDENTITY (1, 1) NOT NULL ,
            [topicid] [int] NOT NULL ,
            [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [replies] [int] NOT NULL ,
            [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [lastpostdate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Messages] (
            [msgid] [int] IDENTITY (1, 1) NOT NULL ,
            [threadid] [int] NOT NULL ,
            [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [date] [datetime] NULL ,
            [message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Forums] ADD
            PRIMARY KEY CLUSTERED
            (
                        [forumid]
            ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Topics] ADD
            PRIMARY KEY CLUSTERED
            (
                        [topicid]
            ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Threads] ADD
            PRIMARY KEY CLUSTERED
            (
                        [threadid]
            ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Messages] ADD
            PRIMARY KEY CLUSTERED
            (
                        [msgid]
            ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Topics] ADD
            FOREIGN KEY
            (
                        [forumid]
            ) REFERENCES [dbo].[Forums] (
                        [forumid]
            )
GO
ALTER TABLE [dbo].[Threads] ADD
            FOREIGN KEY
            (
                        [topicid]
            ) REFERENCES [dbo].[Topics] (
                        [topicid]
            )
GO
ALTER TABLE [dbo].[Messages] ADD
            FOREIGN KEY
            (
                        [threadid]
            ) REFERENCES [dbo].[Threads] (
                        [threadid]
            )
GO
------------------------------------------------------
insert into forums(name,description) values('Developers','Developers Forum');
insert into forums(name,description) values('Database','Database Forum');
insert into forums(name,description) values('Desginers','Designers Forum');
insert into forums(name,description) values('Architects','Architects Forum');
insert into forums(name,description) values('General','General Forum');
insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');
insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');
insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');
insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');
insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');
insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');
insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');
insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');
insert into topics(forumid,name,description) values(1,'Javascript','Javascript');
insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');
insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');
insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');
insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');
insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');
insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');
insert into topics(forumid,name,description) values(4,'Longue','Longue');
insert into topics(forumid,name,description) values(5,'General','General Discussion');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
insert into messages(threadid,author,date,message) values(1,'a@b.com','1/27/2008 2:44:39 PM','Where can I find Java tutorials.');
insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:45:28 PM','Please visit www.java.sun.com');
insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:46:41 PM','Do a Google serach for more tutorials.');
insert into messages(threadid,author,date,message) values(2,'x@y.com','1/27/2008 2:48:53 PM','Please provide some Beginner tutorials.');
insert into messages(threadid,author,date,message) values(3,'p@q.com','1/27/2008 3:12:51 PM','How many finds of layout managers are there?');
insert into messages(threadid,author,date,message) values(2,'l@m.com','2/2/2008 1:06:06 PM','Search on Google.');

View 5 Replies View Related

Removing Duplicate Rows With CTE And Partition - Need Most Recent Entry

Sep 28, 2007

I have tested the code below to remove duplicate table entries based on the field IntOOS. This code works; however, I want to ensure I am removing the oldest entries and keeping the most recent entry. The field to key on this would be something like Max(ID). What would be the best way to ensure I keep the most recent table entry?

/*** Removes duplicate rows from ampfm.rpt_AdtVisitDiag table

by Chuck King 20070928

***/

;WITH CTE

as

(

SELECT

ROW_NUMBER() OVER (Partition By IntOOS Order BY IntOOS) AS ROWID

,DischDate

,AdmDiagCode

,ID

,AdmDiagCodeDesc

,AdmittingDiagnosis

,AnyDx

,DischDx

,ECode

,IntOOS

,PrinDxAnesDesc

,PrinDxAnesInd

,PrinDxCode

,PrinDxCondDesc

,PrinDxCondInd

,PrinDxDesc

,PrinDxEqualsAdmDxYnu

,SecDx10AnesDesc

,SecDx10AnesInd

,SecDx10Code

,SecDx10CondDesc

,SecDx10CondInd

,SecDx10Description

,SecDx11AnesDesc

,SecDx11AnesInd

,SecDx11Code

,SecDx11CondDesc

,SecDx11CondInd

,SecDx11Description

,SecDx12AnesDesc

,SecDx12AnesInd

,SecDx12Code

,SecDx12CondDesc

,SecDx12CondInd

,SecDx12Description

,SecDx13AnesDesc

,SecDx13AnesInd

,SecDx13Code

,SecDx13CondDesc

,SecDx13CondInd

,SecDx13Description

,SecDx14AnesDesc

,SecDx14AnesInd

,SecDx14Code

,SecDx14CondDesc

,SecDx14CondInd

,SecDx14Description

,SecDx15Code

,SecDx15Description

,SecDx1AnesDesc

,SecDx1AnesInd

,SecDx1Code

,SecDx1CondDesc

,SecDx1CondInd

,SecDx1Description

,SecDx2AnesDesc

,SecDx2AnesInd

,SecDx2Code

,SecDx2CondDesc

,SecDx2CondInd

,SecDx2Description

,SecDx3AnesDesc

,SecDx3AnesInd

,SecDx3Code

,SecDx3CondDesc

,SecDx3CondInd

,SecDx3Description

,SecDx4AnesDesc

,SecDx4AnesInd

,SecDx4Code

,SecDx4CondDesc

,SecDx4CondInd

,SecDx4Description

,SecDx5AnesDesc

,SecDx5AnesInd

,SecDx5Code

,SecDx5CondDesc

,SecDx5CondInd

,SecDx5Description

,SecDx6AnesDesc

,SecDx6AnesInd

,SecDx6Code

,SecDx6CondDesc

,SecDx6CondInd

,SecDx6Description

,SecDx7AnesDesc

,SecDx7AnesInd

,SecDx7Code

,SecDx7CondDesc

,SecDx7CondInd

,SecDx7Description

,SecDx8AnesDesc

,SecDx8AnesInd

,SecDx8Code

,SecDx8CondDesc

,SecDx8CondInd

,SecDx8Description

,SecDx9AnesDesc

,SecDx9AnesInd

,SecDx9Code

,SecDx9CondDesc

,SecDx9CondInd

,SecDx9Description

,VisitTypeCode

,accountnumber

,DischVisitTypeCode

FROM ampfm.rpt_AdtVisitDiag

)

--Select * From CTE

Delete From CTE Where ROWID > 1

View 5 Replies View Related

Reporting Services :: SSRS - Removing Extra Rows When Exporting Drill Down Report To Excel?

Jun 18, 2015

I have a drill down report which need to be exported to excel. But, when exporting to excel it shows many unwanted blank rows for a particular record due to the drill down option.

Is it possible to remove it?

I need to exclude the rows in yellow color when exporting to excel.

View 4 Replies View Related

Removing Data

Jul 23, 2005

I have a table that I need to delete some data from and put the deleteddata into a different table.How do I script the following.If Field1 in Table1 is null, remove that row from Table1 and put it ina new table called Table2Regards,Ciar�n

View 5 Replies View Related

Transform To Remove Rows From Data Set A That Match Rows In Data Set B On A Given Key?

Jun 28, 2006

Hi,

I have a common requirement in numerous SSIS processes to take my main input data set and to remove all rows from it that match a second input data set on a given key and output this as the main output. I also want to output (as a second output) all the rows from the main input data set that did match on the given key. However, I don't want to merge in data from the second input, nor am I interested in rows from the second input data set that have no match in the main input.

E.g. If I have the following data:

Main input:
Key Name
--- ----
1 Steve
2 Jamie
3 Donald

Second Input
Key DontCareAboutThisField1
--- -----------------------
1 ...
3 ...
4 ...

Then I would like the following output:

Main Output
Key Name
--- ----
2 Jamie

Second Output
Key Name
--- ----
1 Steve
3 Donald

Can I do this with a standard transform, or will I have to write my own? Any help on this would be greatly appreciated!

Thanks in advance,

Lawrie

View 1 Replies View Related

Removing Blanks In Data Before Import

Apr 3, 2008

I have a comma delimited data file TestData.txt of the form:

123,asss,aweqrrr ,ssdsff , ,2wwwrwrr
434,sff,dgfdgd ,sffsfsfete ,sd ,dff

I want to load this data into SQL Server tables so that the data will be imported without the trailing spaces(blanks) after each data element .
Thus I will like to trim the data of the extra spaces (blanks) before import.

Desired data to be loaded :

123,asss,aweqrrr,ssdsff,,2wwwrwrr
434,sff,dgfdgd,sffsfsfete,sd,dff

Any help will be most welcomed

View 4 Replies View Related

Removing Data From A Sqlserver Database.

Sep 10, 2007

I would like to remove data from a sqlserver database but keep the structure the the db. How would I do this?

View 9 Replies View Related

Removing Columns From A Data Flow

Feb 13, 2006

Seems obvious but I can't see how. How would I remove columns from a data flow so that columns which have been used earlier but are not needed for insert/update are taken out of the flow.

I'm asking because the data ends up in a update statement and the flow has got so big it is unreadable.

Cheers, Al

View 4 Replies View Related

Need Help Removing String Data From Text

Apr 4, 2008

I was wondering if someone could tell me how to use an SQL Function to remove everything from the mess of characters below except 'Test.' Really, any message could be substituted, so it's not as easy to leave everything except the 'Test.' string.

{
tf1ansiansicpg1252deff0deflang1033{fonttbl{f0fnilfcharset0 MS Shell Dlg 2;}{f1fnil MS Shell Dlg 2;}}
{colortbl ;
ed0green0lue0;}
{*generator Msftedit 5.41.15.1507;}viewkind4uc1pard x720cf1f0fs20 Test.f1par
}

It's RichText.

The fs20 could be fs24, fs25, etc. (any numbers). The end of the number would be the point to trim the beginning off.

The '' seems like it would be the point to start trimming the end off.

I've been experimenting with SUBSTRING together with CHARINDEX, but am just not getting even close to coming up with a solution.

Any help would be greatly appreciated

Thank you.

View 1 Replies View Related

Removing Special Characters From Existing Data

Jun 1, 2006

I have a table with several columns of information that I wish to set up some form of schedule to go through this data and remove any special characters that may interfere with other code processes.

Mainly the coma's and the apostrophes. It really messes with my asp pages and scripts when retrieving this information and trying to do other things with it, so I need to figure out how to remove these from the tables so it does not cause these issues.

Knowing this, I cannot figure out how to keep the data in the row/column and just extract the special characters from that data. The other problem is, everything I try requires me to insert either a coma or apostrophe as part of the code string which in lies my issue.

How can I parse through my data, leave the data as-is, but just get rid of coma's, apostrophes, and double quotes?

Does anyone have a basic example that I can use to expand on?

View 1 Replies View Related

Shrinking Database File After Removing Data

Apr 20, 2007

Hi there,

I have a large (420GB) database that has never had data archived off before. I taken a backup to a test server and run a script supplied by the product vendor which has removed a large ammount of old data no longer required.

I have checked within enterprise manager that this data has now gone, however the actual file itself has not shrunk in size. Is there a further step I need to take to get back the space.

Kind Regards

View 2 Replies View Related

Removing Data Base Size Restriction

Jul 10, 2007

About a month ago I restored a data base that had been originally created in MSDE into a full version of SQL Server 2000. I thought that the size restriction would automatically be removed by putting it into the full version of SQL. I was wrong and about a week or so later the data base reached the 2GB size and needed attention. I received some assistance through Microsoft and they walked my client through the process of removing the size restriction. Since then everything has been OK.

Now I am doing something similar. My client has a data base that was originally created in MSDE. We upgraded them to SQL Express and the data base has now grown to 4GB which is the max that SQL Express allows.

We will be installing SQL for Workgroups this week.

My question is this. Is there a setting I must change inside the data base or in SQL for Workgroups that will allow the data base to grow beyond 4GB? We need to let it expand to whatever they need and I cant seem to find any documentation on whether or not I have to change a setting for this data base.

Thanks for your help. Jean

View 3 Replies View Related