Sql Comparing two tables: row

Showing posts with label row. Show all posts

Thursday, March 29, 2012

Concatenate strings in group column

I have following problem:
I want to display a concatenated string in a group row of a report.
The grouping works fine, i can "sum" numerical values etc. as
expected, but i could not find a way to concatenate strings (from the
rows that make up the group). "Join(...)" does not work (gives me an
error message).
I can use "First(...) & Last(...)" to concatenate the string columns
from the first and last row of the group, but couldn't find a way to
concatenate the string columns from all rows...
I am using SQL Server 2005 / Visual Studio 2005.I use a CLR aggregate directly in SQL Server. Hopes your datasource is SQL
Server 2005.
In reports, I use Replace function to change my separator character.
--
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
"PR" <pr_gg@.egal.de> wrote in message
news:76e4156a-8805-4e71-927a-7f2c848f295d@.q77g2000hsh.googlegroups.com...
>I have following problem:
> I want to display a concatenated string in a group row of a report.
> The grouping works fine, i can "sum" numerical values etc. as
> expected, but i could not find a way to concatenate strings (from the
> rows that make up the group). "Join(...)" does not work (gives me an
> error message).
> I can use "First(...) & Last(...)" to concatenate the string columns
> from the first and last row of the group, but couldn't find a way to
> concatenate the string columns from all rows...
> I am using SQL Server 2005 / Visual Studio 2005.
>
>|||On Jan 17, 8:21 am, "Jean-Pierre Riehl" <jean-pierre.ri...@.bewise.fr>
wrote:
> I use a CLR aggregate directly in SQL Server. Hopes your datasource is SQL
> Server 2005.
> In reports, I use Replace function to change my separator character.
> --
> Jean-Pierre Riehlhttp://blog.djeepy1.nethttp://www.bewise.fr
> "PR" <pr...@.egal.de> wrote in message
> news:76e4156a-8805-4e71-927a-7f2c848f295d@.q77g2000hsh.googlegroups.com...
>
thanks for the idea. I do use SQL Server 2005, however, since this is
a "group" column in a report, i can't use SQL to concatenate the
strings as i deal with a grouped subset of the sql query result...|||On Jan 16, 9:31=A0am, PR <pr...@.egal.de> wrote:
> I have following problem:
> I want to display a concatenated string in a group row of a report.
> The grouping works fine, i can "sum" numerical values etc. as
> expected, but i could not find a way to concatenate strings (from the
> rows that make up the group). "Join(...)" does not work (gives me an
> error message).
> I can use "First(...) & Last(...)" =A0to concatenate the string columns
> from the first and last row of the group, but couldn't find a way to
> concatenate the string columns from all rows...
> I am using SQL Server 2005 / Visual Studio 2005.
I'm having a hard time visualizing what you want to display. Do you
want your concantenation in each detail row or just in a group header?
Can you mock up an example?|||On Jan 21, 4:06 pm, toolman <t...@.infocision.com> wrote:
> I'm having a hard time visualizing what you want to display. Do you
> want your concantenation in each detail row or just in a group header?
> Can you mock up an example?
I want the concatenation in each detail row. I have some group "sum"s
that work fine, and want to do the same for a string. Basically it's
just a normal grouping of columns with group sums etc.. just that i
need to concatenate a string besides building the group sums.|||On Jan 22, 4:23=A0am, PR <pr...@.egal.de> wrote:
> On Jan 21, 4:06 pm, toolman <t...@.infocision.com> wrote:
> > I'm having a hard time visualizing what you want to display. =A0Do you
> > want your concantenation in each detail row or just in a group header?
> > Can you mock up an example?
> I want the concatenation in each detail row. I have some group "sum"s
> that work fine, and want to do the same for a string. Basically it's
> just a normal grouping of columns with group sums etc.. just that i
> need to concatenate a string besides building the group sums.
Is this what you want?
=3D"String Expression " & SUM(Fields!Name.Value)
It would look something like:
String Expression 1234.56
If you're wanting to concantenate actual field values, say like if
you're grouping on a combination of fields then:
=3DFields!GroupField1.Value & ", " & Fields!GroupField2.Value
would give you something like City, State or Company, Division
HTH|||On Jan 22, 8:57 pm, toolman <t...@.infocision.com> wrote:
> Is this what you want?
> ="String Expression " & SUM(Fields!Name.Value)
> It would look something like:
> String Expression 1234.56
> If you're wanting to concantenate actual field values, say like if
> you're grouping on a combination of fields then:
> =Fields!GroupField1.Value & ", " & Fields!GroupField2.Value
> would give you something like City, State or Company, Division
> HTH
Sorry, no, i don't want to concatenate strings with the sum value of
the group.
I have a grouping in the report, where a sum value (i.e. =Sum(Fields!
Name.NumValue)) is written to one textfield of the row.
What i want to do is concatenate a string from the groups result set -
pretty much like the "Sum" sums up the numerical values from the
result set over the rows. If "Join" would work it would look like
=Join(Fields!Name.StringValue) for the other text field.. but
unfortunately that does not work.
Any ideas ?|||On Jan 23, 3:50=A0am, PR <pr...@.egal.de> wrote:
> On Jan 22, 8:57 pm, toolman <t...@.infocision.com> wrote:
>
> > Is this what you want?
> > =3D"String Expression " & SUM(Fields!Name.Value)
> > It would look something like:
> > String Expression 1234.56
> > If you're wanting to concantenate actual field values, say like if
> > you're grouping on a combination of fields then:
> > =3DFields!GroupField1.Value & ", " & Fields!GroupField2.Value
> > would give you something like City, State or Company, Division
> > HTH
> Sorry, no, i don't want to concatenate strings with the sum value of
> the group.
> I have a grouping in the report, where a sum value (i.e. =3DSum(Fields!
> Name.NumValue)) is written to one textfield of the row.
> What i want to do is concatenate a string from the groups result set -
> pretty much like the "Sum" sums up the numerical values from the
> result set over the rows. If "Join" would work it would look like
> =3DJoin(Fields!Name.StringValue) for the other text field.. but
> unfortunately that does not work.
> Any ideas ?
I think if you can get your Fields!Name.StringValue values into an
array, you could then use Join() to get what you want. Unfortunately,
I'm not enough of a VB or .NET guy to guide you through that.
Hopefully someone else can jump at this point. Sorry I can't get you
further.|||On Jan 23, 5:03 pm, toolman <t...@.infocision.com> wrote:
> I think if you can get your Fields!Name.StringValue values into an
> array, you could then use Join() to get what you want. Unfortunately,
> I'm not enough of a VB or .NET guy to guide you through that.
> Hopefully someone else can jump at this point. Sorry I can't get you
> further.
Thanks for your effort. Yes, Join() requires an array.. and i have no
idea how to convert the string values into an array to do that (and
could not find anything about it in the documentation).|||Hello, PR.
Did you find any solution to your problem? I have the same issue and I can't
find any solution for this...
Thank you.
--
Alexandra
"PR" wrote:
> On Jan 23, 5:03 pm, toolman <t...@.infocision.com> wrote:
> >
> > I think if you can get your Fields!Name.StringValue values into an
> > array, you could then use Join() to get what you want. Unfortunately,
> > I'm not enough of a VB or .NET guy to guide you through that.
> > Hopefully someone else can jump at this point. Sorry I can't get you
> > further.
> Thanks for your effort. Yes, Join() requires an array.. and i have no
> idea how to convert the string values into an array to do that (and
> could not find anything about it in the documentation).
>|||On Feb 22, 12:13 pm, Alexandra Ribeiro
<AlexandraRibe...@.discussions.microsoft.com> wrote:
> Hello, PR.
> Did you find any solution to your problem? I have the same issue and I can't
> find any solution for this...
> Thank you.
> --
> Alexandra
>
Hi Alexandra,
no, i haven't found a solution for this. Maybe using a sub-report is
an option, but i haven't tried this yet.sql sql

Tuesday, March 27, 2012

Concat tables into one row in view

If I have table1 and table2 with table2 having multiple rows tied to a
single row in table 1.

What I am trying to do is set up a view that has one row that shows
the following
table1.uniqueid, table1.name, table2.row1.detail, table2.row2.detail,
table2.row3.detail

I'd like to be able to do a select on the view and only come back with
one row per widget. If possible, I'd actually like to be able to
concat all the rows from table 2 into one column if that's possible.

table1.uniqueid, table1.name, (table2.row1.detail - table2.row2.detail
- table2.row3.detail), table1.dateCreated

thx
M@.M@. (mattcushing@.gmail.com) writes:

Quote:

Originally Posted by

If I have table1 and table2 with table2 having multiple rows tied to a
single row in table 1.
>
What I am trying to do is set up a view that has one row that shows
the following
table1.uniqueid, table1.name, table2.row1.detail, table2.row2.detail,
table2.row3.detail
>
I'd like to be able to do a select on the view and only come back with
one row per widget. If possible, I'd actually like to be able to
concat all the rows from table 2 into one column if that's possible.
>
table1.uniqueid, table1.name, (table2.row1.detail - table2.row2.detail
- table2.row3.detail), table1.dateCreated

SQL Server MVP Anith Sen has a couple of methods on
http://www.projectdmx.com/tsql/rowconcatenate.aspx.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql sql

Computing several columns for each row in source table and joining to get result

I have come across this several times now, and I cannot figure out how to do
it better. Say I have a simple table called SourceTable:
DECLARE @.sourceTable TABLE
(
data1 INT,
data2 INT,
data3 INT,
data4 INT
)
I need to create a table (view, tv function, etc.) that looks something like
DECLARE @.resultTable TABLE
(
data1 INT,
data2 INT,
data3 INT,
data4 INT,
date1 SMALLDATETIME,
date2 SMALLDATETIME
)
where date1 and date2 are calculated (with functions) using data1...data4
from the same row plus another parameter supplied by the user. So you see
what I want is so simple: For each row in @.sourceTable, evaluate a
table-valued function getDates() that returns a single row containing date1
and date2, and join the result to produce @.resultTable. However, I can't
figure out any syntax to do this straightforwardly.
In some cases where date2 depends on date1, I can use nested queries, so I
can do something like
SELECT
data1,
data2,
data3,
data4,
date1,
date2 = getDate2(@.userInput, date1, data3, data4)
FROM (
SELECT
data1,
data2,
data3,
data4,
date1 = getDate1(@.userInput, data1, data2)
FROM
@.sourceTable
) T1
But recently, I have had several problems where it would be more efficient
and maintainable if I could return both date1 and date2 from a table-valued
function as a single row with two columns. This is because the relationship
between date1 and date2 is more complicated and they can't just be computed
sequentially. My first attempt was to write a TV function that basically
was
CREATE FUNCTION getDates (@.userInput INT, @.data1 INT, @.data2 INT, @.data3
INT, @.data4 INT)
RETURNS @.dates TABLE (date1 SMALLDATETIME, date2 SMALLDATETIME) AS
BEGIN
DECLARE @.date1 SMALLDATETIME
SET @.date1 = getDate1(@.userInput, @.data1, @.data2)
DECLARE @.date2 SMALLDATETIME
SET @.date2 = getDate2(@.userInput, @.data3, @.data4)
IF (@.date1 < @.date2)
SET @.date1 = getDate1(@.date2, @.data1, @.data2)
INSERT INTO @.dates
SELECT @.date1, @.date2
RETURN
END
I tried to join the function with the source table to get my result table as
follows:
SELECT
ST.data1,
ST.data2,
ST.data3,
ST.data4,
D.date1,
D.date2
FROM @.sourceTable ST
INNER JOIN getDates(
@.userInput,
ST.data1,
ST.data2,
ST.data3,
ST.data4) D
but SQL Server always complains when it reaches the 'ST' in the second
argument of getDates(), because apparently ST is not available in that
context. I tried using a cursor to evaluate getDates() for each row in
@.sourceTable and join the result to produce @.resultTable, but something was
just wrong and the query batch would never finish executing in query
analyzer. (I debugged and found that the cursor was implemented properly,
it was just extremely slow or was hanging in QA.) For now, I am using a
several-level-deep nested query that performs the logic of of my getDates()
function. Each query level performs one calculation or condition on one of
the two dates, and the rest of the columns just get carried along. For
example:
SELECT
data1,
data2,
data3,
data4,
date1 = CASE WHEN (date1 < date2)
THEN getDate1(date2, data1, data2)
ELSE date1
END,
date2
FROM (
SELECT
data1,
data2,
data3,
data4,
date1,
date2 = getDate2(@.userInput, data3, data4)
FROM (
SELECT
data1,
data2,
data3,
data4,
date1 = getDate1(@.userInput, data1, data2)
FROM
@.sourceTable
) RT1
) RT2
The query is actually a few levels deeper because I have to calculate other
things based on date1, and there are many more columns. This is horrible in
terms of readability and maintanability because the logic is distributed
throughout each level of the query, and I have to repeat all the columns at
each level. If I could return more than one column from a correlated
subquery, I would be fine, but I don't believe this is possible. Can
someone please help?Well, at least I know it wasn't just me. Thanks!
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23hvbu$gEGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Dustbort,
> SQL Server 2000 and earlier do not support "correlated joins",
> which is what you are trying to write. In your example, the
> right-hand table is a table-valued function that is a different
> table for each row of the left-hand table.
> In SQL Server 2005, this can be done with the new
> APPLY operator. In 2000, there is no easy way,
> though it's possible that there is an easier way to solve
> your specific problem.
> Steve Kass
> Drew University
>
> dustbort wrote:
>

Sunday, March 11, 2012

complicated Join - duplicate row problem

Hi
I had a similar problem awhile back and it was solved here. Now it has
gotten more complex. I have removed uneeded stuff here to keep this
simple.:
I am sure I know why I am getting the results I am, but don't know
how (or if) there is a way around it. If someone can help, I will be
mighty excited and impressed !
Here goes:
Given a table definition:
CREATE TABLE table1
(col1 varchar(20),
col2 varchar(20),
col3 integer,
CONSTRAINT PK_table1 PRIMARY KEY (col1,col2))
and then (assuming table2 exists)
ALTER TABLE table1
ADD CONSTRAINT FK_table1_table2
FOREIGN KEY (col1)
REFERENCES table2 (col1)
REQUIREMENT:
I would like to select some schema information about this table. Among
other things, I want the column name, data type, and Primary Key and
Foreign Key information. For the Primary Key and Foreign Key, all I
need to know is if one or both of these attributes applies to a
column.
Therefore, I would like my result records to look like this:
name | type | PK_col | FK_col
--+--+--+--
col1 | varchar | PK | FK
col2 | varchar | PK |
col3 | integer | |
My problem is getting the PK and FK on the same row. My results are
currently like this:
name | type | PK_col | FK_col
--+--+--+--
col1 | varchar | PK |
col1 | varchar | | FK
col2 | varchar | PK |
col3 | integer | |
HERE IS THE QUERY:
SELECT cols.COLUMN_NAME as name,cols.DATA_TYPE as type,
PK_COL = case when T.CONSTRAINT_TYPE = 'PRIMARY KEY' then 'PK'
else '' END,
FK_Col = case when T.CONSTRAINT_TYPE = 'FOREIGN KEY' then 'FK'
else '' END
FROM Test.INFORMATION_SCHEMA.COLUMNS cols
left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
on cols.table_name = K.TABLE_NAME
and cols.column_name = K.column_name
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
on k.table_name = t.table_name
and K.CONSTRAINT_NAME = T.CONSTRAINT_NAME
and (T.CONSTRAINT_TYPE = 'PRIMARY KEY'
or T.CONSTRAINT_TYPE = 'FOREIGN KEY' or T.CONSTRAINT_TYPE = 'UNIQUE')
WHERE cols.TABLE_NAME = 'table1'
ORDER BY cols.ORDINAL_POSITION
So, Col1 is duplicated because there are two (2) entries (PK & FK) in
key_column_usage for that column.
Is there some way to combine these two result rows together taking the
PK info from 1 and the FK from the other?
Thanks
JeffHi, Jeff
Did you read my response to your previous post ?
http://groups.google.com/group/micr...br />
a2bf3ce9
It's exactly what you are requesting now...
Razvan|||Sorry, I had missed your earlier reply because it didn't show up in my
news reader under the question. I see it now. My mistake.
This looks good and makes sense.
Thank you very very much !!
Jeff
On 12 Jan 2006 23:28:37 -0800, "Razvan Socol" <rsocol@.gmail.com>
wrote:

>Hi, Jeff
>Did you read my response to your previous post ?
>http://groups.google.com/group/micr...r />
9a2bf3ce9
>It's exactly what you are requesting now...
>Razvan|||On Fri, 13 Jan 2006 06:38:33 GMT, Jeff User wrote:
(snip)
>Therefore, I would like my result records to look like this:
>name | type | PK_col | FK_col
>--+--+--+--
> col1 | varchar | PK | FK
> col2 | varchar | PK |
> col3 | integer | |
>My problem is getting the PK and FK on the same row. My results are
>currently like this:
>name | type | PK_col | FK_col
>--+--+--+--
> col1 | varchar | PK |
> col1 | varchar | | FK
> col2 | varchar | PK |
> col3 | integer | |
(snip)
Hi Jeff,
Just a few simple changes to the query should suffice:
SELECT cols.COLUMN_NAME as name,cols.DATA_TYPE as type,
PK_COL = MAX(case when T.CONSTRAINT_TYPE = 'PRIMARY KEY' then 'PK'
else '' END),
FK_Col = MAX(case when T.CONSTRAINT_TYPE = 'FOREIGN KEY' then 'FK'
else '' END)
FROM Test.INFORMATION_SCHEMA.COLUMNS cols
left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
on cols.table_name = K.TABLE_NAME
and cols.column_name = K.column_name
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
on k.table_name = t.table_name
and K.CONSTRAINT_NAME = T.CONSTRAINT_NAME
and (T.CONSTRAINT_TYPE = 'PRIMARY KEY'
or T.CONSTRAINT_TYPE = 'FOREIGN KEY' or T.CONSTRAINT_TYPE = 'UNIQUE')
WHERE cols.TABLE_NAME = 'table1'
ORDER BY cols.ORDINAL_POSITION
GROUP BY cols.COLUMN_NAME, cols.DATA_TYPE, cols.ORDINAL_POSITION
Hugo Kornelis, SQL Server MVP

Thursday, March 8, 2012

complex tables - want to join to get one row of results from multiple rows

Hi there

This is a hard problem that I have - I have only been using sql for a couple of weeks and have gone past my ability level quickly! The real tables are complex but I will post a simple and a real version with the hope someone can help me.

Any help would be much appreciated - I would also be happy to pay someone to actually do it if it takes time to work out as I know that its hard when all your help is free :)
================================================
SIMPLE VERSION
Table 1 Dog breeds
dogbreedID, dogBreedName, colour
1,labrador,golden
2,beagle, tricolour
3,great dane, marle

Table 2 - maps criteria to dog breeds
dogbreedID, criteriaID, value, location
3,2,easy to train, c:/filepath2
1,1,good with children, c:/filepath
1,2,easy to train, c:/filepath2
2,1,good with children, c:/filepath
3,3,stranger friendly, c:/filepath3

So that leads to table 3 sitting behind the scenes not used in this query:
criteriaID, value, location
1,good with children, c:/filepath
2,easy to train, c:/filepath2
3,stranger friendly, c:/filepath3

I want a view that has the following:
dogbreedID, dogBreedName, colour, criteriaID1, value1, location1, criteriaID2, value2, location2, criteriaID3, value3, location3,criteriaID4, value4, location4

1,labrador,golden,1,good with children, c:/filepath,2,easy to train, c:/filepath2,NULL,NULL,NULL,NULL, NULL, NULL

2,beagle, tricolour,1,good with children,NULL, NULL, NULL,NULL, NULL, NULL,NULL, NULL, NULL

3,great dane, marle,NULL, NULL, NULL,2,easy to train, c:/filepath2,3,stranger friendly, c:/filepath3

================================================== =====
more complicated view - you can see each table is actually a combination of table values but I dont think that matters to this problem - the above example is fine but I am not very good with this so may have left somehting out that you can derive from the example below:
Table 1:
SELECT distinct dbo.BREED_dogBreeds.breedId, dbo.BREED_dogBreeds.breedName, dbo.BREED_dogBreeds.alternativeName, dbo.BREED_dogBreeds.shortDesc,
dbo.BREED_dogBreeds.katShortDesc, dbo.BREED_dogBreeds.longDesc, dbo.BREED_dogBreeds.katLongDesc,
dbo.BREED_dogBreeds.thingsToConsider, dbo.BREED_dogBreeds.temperament, dbo.BREED_dogBreeds.history, dbo.BREED_dogBreeds.feeding,
dbo.BREED_tblCountry.Country_Name, dbo.BREED_dogBreeds.colour, dbo.BREED_dogBreeds.breedProfileLink, dbo.BREED_Grooming.groomText,
dbo.BREED_GroomFrequencyValues.value, dbo.BREED_Training.intelligence, dbo.BREED_Training.trainingNotes, dbo.BREED_Training.exerciseText,
dbo.BREED_Training.exerciseTime, dbo.BREED_Training.timePerDay, dbo.BREED_Suitability.idealOwner, dbo.BREED_Size.size,
dbo.BREED_sizes.bheightmin, dbo.BREED_sizes.bheightmax, dbo.BREED_sizes.bweightmin, dbo.BREED_sizes.bweightmax,
dbo.BREED_sizes.dheightmin, dbo.BREED_sizes.dheightmax, dbo.BREED_sizes.dweightmin, dbo.BREED_sizes.dweightmax,
dbo.BREED_Sociability.compatibility FROM dbo.BREED_Sociability RIGHT OUTER JOIN
dbo.BREED_dogBreeds ON dbo.BREED_Sociability.sociabilityID = dbo.BREED_dogBreeds.sociabilityID LEFT OUTER JOIN
dbo.BREED_Size RIGHT OUTER JOIN
dbo.BREED_sizes ON dbo.BREED_Size.id = dbo.BREED_sizes.sizeid ON
dbo.BREED_dogBreeds.sizeId = dbo.BREED_sizes.breedSizeId LEFT OUTER JOIN
dbo.BREED_Suitability ON dbo.BREED_dogBreeds.suitabilityID = dbo.BREED_Suitability.suitabilityID LEFT OUTER JOIN
dbo.BREED_Training ON dbo.BREED_dogBreeds.trainID = dbo.BREED_Training.trainID LEFT OUTER JOIN
dbo.BREED_GroomFrequencyValues RIGHT OUTER JOIN
dbo.BREED_Grooming ON dbo.BREED_GroomFrequencyValues.gfvID = dbo.BREED_Grooming.gfvID ON
dbo.BREED_dogBreeds.groomID = dbo.BREED_Grooming.groomID LEFT OUTER JOIN
dbo.BREED_tblCountry ON dbo.BREED_dogBreeds.country = dbo.BREED_tblCountry.Country_ID

Table 2:
SELECT [breedId]
,[breedCriteriaID]
,[value]
,[icon]
FROM [v1vw1n_dogmatch].[dbo].[vbreedCriterias]
where levelID>=3
order by breedId, breedCriteriaID asc

TABLE 3
SELECT [breedCriteriaID]
,[criteriaValue]
,[icon]
FROM [v1vw1n_dogmatch].[dbo].[BREED_Criteria]

Table1
186Afghan HoundTazi, Baluchi HoundA strikingly beautiful dog with dignified poise.Afghans are kept primarily as show dogs and can also be used for lure coursing. They are extremely loving and loyal to their owners and are gentle souled and good with children. Afghans can make companion dogs but their considerable needs means that only devoted owners keep them. Aloof with strangers but affectionate and loyal to their owners. Can be very clown like at play and are very people oriented. Love children and being included in family life. Can become introverted if excluded from social situations whilst pups.An ancient breed, the afghan looks as classy as its pedigree. Afghans were used in Afghanistan to protect the flocks and would hunt and kill panthers, leopards and other large predators. The first dog to be shown was in the UK in 1907 having previously been banned from export. Afghans can be fussy with their food and it is better to instill good eating habits when they are pups and ideally treats should be avoided. AfghanistanAfghans are a grooming salons dream come true - they demand regular grooming and can quickly become knotted and tangled without it. DailyAll dogs are bright but Afghans may not quite earn the MENSA of the dog world.Afghans can be hard to train with lots of perseverance needed. Highly strung, stubborn and sensitive natured dog making them difficult to train. Training cannot be rushed and as they are sensitive souls and it is very important not treat them harshly. Sometimes difficult to housebreak. As puppies, Afghans often appear awkward, with uneven growth, gawkiness and loose limbs, and for this reason, exercise must be carefully monitored to avoid injury to their growing bones.00Suitable for the experienced, comitted dog owner with time on their hands and a great love for the breed.large63cm69cm23kg25kg68cm74cm25kg (55lb)28kg (62lb)They are hunting stock and love to chase anything that moves so perhaps not the ideal dog to share a home with cats and small animals!
187Bluetick CoonhoundNULLNot found in Australia, the Bluetick Coonhound is a friendly hound that makes an excellent tracking or scent dog.NULLThis breed originated in the states and has a smooth, dense, tri colour coat. The base colour is white with heavy ticking of black and tan markings over their chest, eyes, muzzle, lower legs and feet. This breed is recognised as a competitive, fearless, dedicated hunter. The Bluetick has a typical hound bawl and so is not the quietest of dogs.NULLThis dog needs a lot of exercise. They love to do jobs, and keep busy. They need to be exercised vigorously or they run the danger of becoming destructive and will howl excessivly.The Coonhound is deeply devoted, fearless, attentive and loyal. They make very good guardians and family companions. They are reserved with strangers, but are not aggressive towards them. They are not the best with other animals, and get along better with older, considerate children.The Bluetick originated in Louisiana at the beginning of the 20th century. They were developed from crosses between the English Coonhound, the Foxhound and the french Grand Bleu de Gascogne. Their tricoloured, blue-speckled coat sets them apart from other Coonhound breeds. Originally they were registered as a variant of the English Coonhound, but in 1946 they were given separate recognition as a distinct breed in their own right.Today the English Coonhound is sometimes referred to as the Redtick. The original, old-fashioned Bluetick dog, which was larger and slower than the modern type, began to loose ground because they did less well in the increasingly popular field competitions and night trials. The smaller, faster version began to eclipse them and became one of the most popular and numerous of all coonhound breeds. This upset the traditionalists, who preferred the old-style Big Blue , and some of them reacted by switching their allegiance to other large-bodied breeds, such as the Blue Gascon, and the Majestic.This breed is not fussy when it comes to their diet, but they have quite a healthy appetite.United States of AmericaNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
188Australian Cattle DogCattledog, Queensland Heeler, Heeler, Blue Heeler, Red Heeler, BlueyA strong compact working dog, the Australian Cattle Dog is one of the most popular breeds of dog in Australia.A strong compact working dog, the Australian Cattle Dog is one of the most popular breeds of dog in Australia.The Australian Cattle Dog is a strong compact working dog with a combination of substance, power, balance and hard muscular condition that conveys the impression of great agility, strength and endurance with the ability and willingness to carry out his allotted task however arduous. His head is wedge shaped with a broad skull and muscular cheeks and oval shaped, dark brown eyes that express alertness and intelligence and have a warning or suspicious glint when approached by strangers. His ears are broad at base, muscular, pricked and moderately pointed. His rain resistant coat is a smooth double coat with a close, hard outer coat and a short dense undercoat AND comes in two colours: Blue: Blue, blue-mottled or blue speckled with tan markings. Red: An even red speckle all over, including the undercoat, with or without darker red markings on the head. The pups are born white, developing their colour gradually from approximately three weeks of age. The Australian Cattle Dog is a strong compact working dog with a combination of substance, power, balance and hard muscular condition that conveys the impression of great agility, strength and endurance with the ability and willingness to carry out his allotted task however arduous. His head is wedge shaped with a broad skull and muscular cheeks and oval shaped, dark brown eyes that express alertness and intelligence and have a warning or suspicious glint when approached by strangers. His ears are broad at base, muscular, pricked and moderately pointed. His rain resistant coat is a smooth double coat with a close, hard outer coat and a short dense undercoat AND comes in two colours: Blue: Blue, blue-mottled or blue speckled with tan markings. Red: An even red speckle all over, including the undercoat, with or without darker red markings on the head. The pups are born white, developing their colour gradually from approximately three weeks of age. The Australian Cattle Dog has a strong herding instinct and when playing may nip the heels of children.The Australian Cattle Dog needs a job, companionship and activity for the mind and body, all day, every day. Easily bored, he can become noisy and/or destructive. Renowned for his protectiveness and loyalty to master and property, he is very selective as to who is friend or foe. In 1840, Thomas Hall, a landowner in New South Wales, imported two smooth-haired blue merle Scotch Collies and crossed their progeny with the Dingo. The resulting litters became known as Hall's Heelers. The progeny were generally of Dingo type with the colour being either red or blue merle and were valued for their ability to handle wild cattle, stamina to travel great distances over all types of terrain, and their endurance in extremes of temperature. Later, Hall's Heelers were crossed with a Dalmatian, which changed the merle colour to red or blue speckle and instilled in the dogs a love of horses and protectiveness toward master and property. A further cross was made to the Kelpie to produce highly intelligent, controllable workers resembling thickset Dingoes and with peculiar markings known to no other dog. In 1903 a standard for the breed was drawn up and from these beginnings the Australian Cattle Dog has developed into one of the most popular breeds of dog in Australia today. AustraliaMinimal grooming although weekly brushing is required to remove dead hair. Does shed seasonally.Weekly - at homeHe is loving, playful, and eager to please his owner. Very quick to learn.Unless you can give him extensive exercise do not even consider owning an Australian Cattle Dog. He was bred to work all day in hard conditions and will become bored if not given sufficient exercise.602Single guy or gal, athletic, able to have the dog with them most of the time. Not a first time dog owner. Families with older children.medium43cm (17")48cm (19")16kg (35lb)20kg (44lb)46cm (18")51cm (20")16kg (35lb)20kg (44lbs)Not good with dogs of the same sex. OK with cats if raised with the cat since puppyhood. Not good with other small mammals.

TABLE 2
1861suits older children/Images/icons/older_children.gif
1862suits younger children/Images/icons/suits_young_children.gif
1863suits elderly/disabled. Not too boisterous/Images/icons/suits_elderly_disabled.gif
1866easy to transport/Images/icons/transport.gif
1868grooming needs/Images/icons/grooming_needs.gif
18610distress/destruction when left alone/Images/icons/distressed_when_alone.gif
18612Shedding / Hair loss/Images/icons/shedding_hairloss.gif
18615Energy Level/Images/icons/energy_levels.gif
18616Affection with family/Images/icons/affectionate.gif
18622may bite intruder/Images/icons/bite_intruder.gif
18623watchdog skills/Images/icons/watchdog_skills.gif
1871suits older children/Images/icons/older_children.gif
1876easy to transport/Images/icons/transport.gif
18710distress/destruction when left alone/Images/icons/distressed_when_alone.gif
18712Shedding / Hair loss/Images/icons/shedding_hairloss.gif
18713stranger friendly/Images/icons/stranger_friendly.gif
18715Energy Level/Images/icons/energy_levels.gif
18716Affection with family/Images/icons/affectionate.gif
18722may bite intruder/Images/icons/bite_intruder.gif
18723watchdog skills/Images/icons/watchdog_skills.gif
1881suits older children/Images/icons/older_children.gif
1883suits elderly/disabled. Not too boisterous/Images/icons/suits_elderly_disabled.gif
1884cattle friendly/Images/icons/cattle_friendly.gif
1886easy to transport/Images/icons/transport.gif
18810distress/destruction when left alone/Images/icons/distressed_when_alone.gif
18811trainability/Images/icons/trainable.gif
18812Shedding / Hair loss/Images/icons/shedding_hairloss.gif
18813stranger friendly/Images/icons/stranger_friendly.gif
18814How often this breed is found in the pound/Images/icons/found_in_pount.gif
18815Energy Level/Images/icons/energy_levels.gif
18816Affection with family/Images/icons/affectionate.gif
18819Availability/Images/icons/availability.gif
18820cat friendly/Images/icons/cat_friendly.gif
18822may bite intruder/Images/icons/bite_intruder.gif
18823watchdog skills/Images/icons/watchdog_skills.gif
1891suits older children/Images/icons/older_children.gif
1892suits younger children/Images/icons/suits_young_children.gif
1898grooming needs/Images/icons/grooming_needs.gif
18910distress/destruction when left alone/Images/icons/distressed_when_alone.gif
18912Shedding / Hair loss/Images/icons/shedding_hairloss.gif
18913stranger friendly/Images/icons/stranger_friendly.gif
18914How often this breed is found in the pound/Images/icons/found_in_pount.gif
18915Energy Level/Images/icons/energy_levels.gif
18916Affection with family/Images/icons/affectionate.gif
18919Availability/Images/icons/availability.gif
18923watchdog skills/Images/icons/watchdog_skills.gif
1901suits older children/Images/icons/older_children.gif
1903suits elderly/disabled. Not too boisterous/Images/icons/suits_elderly_disabled.gif
1906easy to transport/Images/icons/transport.gif
19010distress/destruction when left alone/Images/icons/distressed_when_alone.gif
19011trainability/Images/icons/trainable.gif
19013stranger friendly/Images/icons/stranger_friendly.gif
19014How often this breed is found in the pound/Images/icons/found_in_pount.gif
19015Energy Level/Images/icons/energy_levels.gif
19016Affection with family/Images/icons/affectionate.gif
19019Availability/Images/icons/availability.gif
19022may bite intruder/Images/icons/bite_intruder.gif
19023watchdog skills/Images/icons/watchdog_skills.gif
19024Gay Icon/Images/icons/gay_icon.gif
1921suits older children/Images/icons/older_children.gif
1922suits younger children/Images/icons/suits_young_children.gif
1923suits elderly/disabled. Not too boisterous/Images/icons/suits_elderly_disabled.gif
1924cattle friendly/Images/icons/cattle_friendly.gif
1925bunny/guinea pig friendly/Images/icons/bunny_friendly.gif
1926easy to transport/Images/icons/transport.gif
19210distress/destruction when left alone/Images/icons/distressed_when_alone.gif
19211trainability/Images/icons/trainable.gif
19212Shedding / Hair loss/Images/icons/shedding_hairloss.gif
19213stranger friendly/Images/icons/stranger_friendly.gif
19215Energy Level/Images/icons/energy_levels.gif
19216Affection with family/Images/icons/affectionate.gif
19217dog friendly/Images/icons/dog_friendly.gif
19219Availability/Images/icons/availability.gif
19220cat friendly/Images/icons/cat_friendly.gif
19223watchdog skills/Images/icons/watchdog_skills.gif

etcHi there

I got a response on another board. Basically I just used
SELECT dogBreedID,
crit1value = MIN(CASE criteriaID WHEN 1 THEN value END),
crit1location = MIN(CASE criteriaID WHEN 1 THEN location END),
crit2value = MIN(CASE criteriaID WHEN 2 THEN value END),
crit2location = MIN(CASE criteriaID WHEN 2 THEN location END),
...
FROM tbl
GROUP BY dogBreedID

and joined that to the first table.

Fantastic!

complex sql statement

I need to get multiple values for each row in a database, then do a calculation and insert the calculation and the accountnumber related to the calculation the data, into a different column. I get an error trying it this way...there is no real identifier, it is jsut something that needs to get done per row...any ideas on how I can accomplish this?

Declare @.NetCommissiondecimal

Declare @.AccountNumbervarchar(50)

Set

@.NetCommission=(select(CommissionRebate* Quantity)

from

Account A

Join

Trades Ton A.AccountNumber= T.AccountNumber)

Set

@.AccountNumber=(select A.AccountNumber

from

cmsAccount A

Join

Trades Ton A.AccountNumber= T.AccountNumber)

Insert

into Transaction

(

Payee

Deposit

AccountNumber

)

Values

(

'Account Credit'

@.NetCommission

@.AccountNumber

)

Insert

intoTransaction

(

Payee

........

Transaction is the key word,you have to choose another table name.

|||Or always embed such object names in [] or "" to avoid "syntax error":

Insert
into [Transaction]

(

Payee...|||

Even with the table name changed I am still getting a subquery error

Subquery returned more than 1 value.

All I really want to do is run a calculation on ech row in the database...any ideas?

Declare @.NetCommissiondecimal

Declare @.AccountNumbervarchar(50)

Set

@.NetCommission=(

select

(CommissionRebate* Quantity)

from

cmsAccount A

Join

cmsTrades Ton A.AccountNumber= T.AccountNumber)

Set

@.AccountNumber=(select A.AccountNumber

from

cmsAccount A

Join

cmsTrades Ton A.AccountNumber= T.AccountNumber)

Insert

into TradeTransaction

(

Payee

Deposit

AccountNumber

)

Values

(

'Account Credit'

@.NetCommission

@.AccountNumber

)

|||Obviously the error indicates?that?some?subquery?returns?more?than?1?value?while?you're?trying?to?assign?the?returned?values?to?a?single?variable.?This?is?a?bad?logic?as?there?is?no?array?in?T-SQL.?So?my?suggestion?would?be?to?use?some?aggregation?funciton?or?TOP?keyword?to?restrict?the?subquery?to?return?only?1?value, for example:

Set @.NetCommission =
(select top 1 (CommissionRebate * Quantity)
from cmsAccount A Join
cmsTrades T on A.AccountNumber = T.AccountNumber)

Or:

Set @.NetCommission =
(select max(CommissionRebate * Quantity)
from cmsAccount A Join
cmsTrades T on A.AccountNumber = T.AccountNumber)|||

This gets me the two values I need, but I then need to insert these two values into a different table... how would I go about doing this?

Select

sum(A.CommissionRebate* T.Quantity)As NetCommission, T.AccountNumber

from

cmsTrades T

Join

cmsAccount Aon A.AccountNumber= T.AccountNumber

where

T.TradeDate='11/13/2006'

GROUP

BY T.AccountNumber

|||

Ok this one works but it only inserts the first row and runs successfully, but does not go on to insert the other rows...

Declare

@.NetCommissiondecimal

Declare

@.AccountNumberVarchar(50)

Select

@.NetCommission=Sum(A.CommissionRebate* T.Quantity), @.AccountNumber= T.AccountNumber

from

cmsTrades T

Join

cmsAccount Aon A.AccountNumber= T.AccountNumber

where

T.TradeDate='11/13/2006'

GROUP

BY T.AccountNumber

Insert

into appTransaction

(

Payee

Payment

AccountNumber

)

Values

(

'Client Credit'

@.NetCommission

@.AccountNumber

)

|||If you want to insert multiple rows you need to use Cursor to skip through the rows to be inserted, or store the rows in a temple table so that you can?fetch rows from it?into?some?table?in?a?single?insert?statement.|||

I did this with the cursor idea, worked for a second and now I am getting this message: Error converting data type varchar to decimal.

DECLARE

appCursorCursor

For

Select

T.AccountNumber,sum(CommissionRebate)/count(*)*sum(Quantity)as NetCommission

from

cmsTrades T

Join

cmsCalcs con c.AccountNumber= T.AccountNumber

where

T.TradeDate=CAST(YEAR(getdate()) as varchar) + RIGHT('00'+CAST(MONTH(getdate()) as varchar), 2) + RIGHT('00'+CAST(DAY(getdate())-1 as varchar), 2)

and

.Month= 11

GROUP

BY T.AccountNumber

Open

appCursor

Declare

@.NetCommissiondecimal, @.AccountNumberVarchar(50)

Fetch

Nextfrom appCursorInto @.NetCommission, @.AccountNumber

While

(@.@.Fetch_Status<>-1)

Begin

(@.@.Fetch_Status<>-2)

Insert

into appTransaction

(

Payee

Payment

AccountNumber

)

Values

(

'Client Credit'

@.NetCommission

@.AccountNumber

)

Fetch

Nextfrom appCursorInto @.NetCommission, @.AccountNumber

End

appCursor

DEALLOCATE

appCursor

|||I found this:

DECLARE
appCursor Cursor For
Select
T.AccountNumber, sum(CommissionRebate) / count(*) * sum(Quantity) as NetCommission
from
cmsTrades T
.....
Fetch Next from appCursor Into @.NetCommission, @.AccountNumber

Shouldn't this be?Fetch Next from appCursor Into @.AccountNumber, @.NetCommission? The same order as the returned fields in the select query?|||

Forget the cursor. You want to stay away from those as much as possible. Keep with set-based logic; it's the SQL way! :-)

Use this sort of approach instead:

INSERT INTO TradeTransaction
(
Payee,
Deposit,
AccountNumber
)
SELECT
'Account Credit',
CommissionRebate * Quantity,
A.AccountNumber
FROM
cmsAccount A
INNER JOIN
cmsTrades T on A.AccountNumber = T.AccountNumber

|||that works great, would there be an easy way to sum up the inserts by AccountNumber so instead of 36 inserts for one accountnumber I would have just 1 for each accountnumber for that day?|||

Yes, you might try this:

INSERT INTO TradeTransaction
(
Payee,
Deposit,
AccountNumber
)
SELECT
'Account Credit',
SUM(CommissionRebate * Quantity),
A.AccountNumber
FROM
cmsAccount A
INNER JOIN
cmsTrades T on A.AccountNumber = T.AccountNumber
GROUP BY
A.AccountNumber

|||That did the trick perfectly...thank you!

Friday, February 24, 2012

Complex Duplicate Row Issue (Need To Keep Shorter Value)

I believe that this idea will work for =the problem as you have described.
CREATE TABLE =#MyTable ( RowID =int IDENTITY , SKU int , Descript varchar(25) )
SET NOCOUNT ON
INSERT INTO #MyTable VALUES ( =111, 'Glass Vase' )INSERT INTO #MyTable VALUES ( 111, 'Glass' )INSERT INTO =#MyTable VALUES ( 333, 'Apple' ) DELETE #MyTableWHERE =Descript =3D ( SELECT min ( Descript ) &n=bsp; FROM #MyTable &=nbsp; GROUP BY sku = HAVING count( SKU ) > 1 &n=bsp; )
SELECT *FROM #MyTable
DROP TABLE =#MyTable
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
=wrote in message news:1153932460.568433.293330@.s13g2000cwa.googlegroups.com...>a =client of mine caused a bit of an issue with one of our SQL Server> =databases. They modified the database to create duplicate SKU values.> Each =SKU value has a different Name column value.> > What I need to =do is delete the shorter value.> > SKU Name> 111 Glass Vase> =111 Glass> 333 Apple> > so I =need to get rid of SKU 111 w/ the Name value of Glass.> > What I =have done is created a view that give me a listing of all the> row values =with duplicate SKUs, but I am not sure how to do the Len> comparison =and return the shorter value.> > Any help is =appreciated.> > Thanks!>

--=_NextPart_000_0979_01C6B0A2.71C31A40--|||Arnie, if two SKUs have the same description that is going to erase
some good data.
Roy
On Wed, 26 Jul 2006 10:58:36 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>I believe that this idea will work for the problem as you have described.
>CREATE TABLE #MyTable
> ( RowID int IDENTITY
> , SKU int
> , Descript varchar(25)
> )
>SET NOCOUNT ON
>INSERT INTO #MyTable VALUES ( 111, 'Glass Vase' )
>INSERT INTO #MyTable VALUES ( 111, 'Glass' )
>INSERT INTO #MyTable VALUES ( 333, 'Apple' )
>DELETE #MyTable
>WHERE Descript = ( SELECT min ( Descript )
> FROM #MyTable
> GROUP BY sku
> HAVING count( SKU ) > 1
> )
>SELECT *
>FROM #MyTable
>DROP TABLE #MyTable|||Yep, you are right. Drat, back to the drawing board...
(That wouldn't ever happen, would it?) ;-)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:7ebfc25dgml1a5dstv0a1retue37n3dcdu@.4ax.com...
> Arnie, if two SKUs have the same description that is going to erase
> some good data.
> Roy
> On Wed, 26 Jul 2006 10:58:36 -0700, "Arnie Rowland" <arnie@.1568.com>
> wrote:
>>I believe that this idea will work for the problem as you have described.
>>CREATE TABLE #MyTable
>> ( RowID int IDENTITY
>> , SKU int
>> , Descript varchar(25)
>> )
>>SET NOCOUNT ON
>>INSERT INTO #MyTable VALUES ( 111, 'Glass Vase' )
>>INSERT INTO #MyTable VALUES ( 111, 'Glass' )
>>INSERT INTO #MyTable VALUES ( 333, 'Apple' )
>>DELETE #MyTable
>>WHERE Descript = ( SELECT min ( Descript )
>> FROM #MyTable
>> GROUP BY sku
>> HAVING count( SKU ) > 1
>> )
>>SELECT *
>>FROM #MyTable
>>DROP TABLE #MyTable|||Here is my version of finding the rows you want to keep.
SELECT *
FROM Product as P
WHERE P.Name = (select min(S.Name) from Product as S
where P.SKU = S.SKU
and LEN(S.Name) = (select max(len(name))
from Product as X
where P.SKU = X.SKU))
Untested, of course, as is my version of the DELETE:
DELETE Product
WHERE EXISTS
(select * from Product as Z
where Product.SKU = Z.SKU
and Product.Name <> Z.Name
and LEN(Product.Name) < LEN(Z.Name))
Roy Harvey
Beacon Falls, CT|||Roy Harvey wrote:
> Here is my version of finding the rows you want to keep.
> SELECT *
> FROM Product as P
> WHERE P.Name => (select min(S.Name) from Product as S
> where P.SKU = S.SKU
> and LEN(S.Name) => (select max(len(name))
> from Product as X
> where P.SKU = X.SKU))
> Untested, of course, as is my version of the DELETE:
> DELETE Product
> WHERE EXISTS
> (select * from Product as Z
> where Product.SKU = Z.SKU
> and Product.Name <> Z.Name
> and LEN(Product.Name) < LEN(Z.Name))
This worked wonderfully. Thank you, my friend.|||Just to note that my two queries could involve different results. The
first one returned ONE row, even if the two rows had the same
(longest) length description. The DELETE only deleted rows with
shorter descriptions.
It would be worth double checking:
SELECT SKU, max(Name), min(Name), count(*)
FROM Product
GROUP BY SKU
HAVING COUNT(*) > 1
ORDER BY SKU, 2, 3
Roy
On 26 Jul 2006 11:28:48 -0700, carpeaqua@.gmail.com wrote:
>Roy Harvey wrote:
>> Here is my version of finding the rows you want to keep.
>> SELECT *
>> FROM Product as P
>> WHERE P.Name =>> (select min(S.Name) from Product as S
>> where P.SKU = S.SKU
>> and LEN(S.Name) =>> (select max(len(name))
>> from Product as X
>> where P.SKU = X.SKU))
>> Untested, of course, as is my version of the DELETE:
>> DELETE Product
>> WHERE EXISTS
>> (select * from Product as Z
>> where Product.SKU = Z.SKU
>> and Product.Name <> Z.Name
>> and LEN(Product.Name) < LEN(Z.Name))
>
>This worked wonderfully. Thank you, my friend.|||What would happen if there were 2 rows with the same SKU and the same length
[Name]?
Would they BOTH be left -or deleted, and without indication of an incomplete
update?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:qkdfc2t451agl5stchnp57po3gur2vj7bk@.4ax.com...
> Just to note that my two queries could involve different results. The
> first one returned ONE row, even if the two rows had the same
> (longest) length description. The DELETE only deleted rows with
> shorter descriptions.
> It would be worth double checking:
> SELECT SKU, max(Name), min(Name), count(*)
> FROM Product
> GROUP BY SKU
> HAVING COUNT(*) > 1
> ORDER BY SKU, 2, 3
> Roy
> On 26 Jul 2006 11:28:48 -0700, carpeaqua@.gmail.com wrote:
>>Roy Harvey wrote:
>> Here is my version of finding the rows you want to keep.
>> SELECT *
>> FROM Product as P
>> WHERE P.Name =>> (select min(S.Name) from Product as S
>> where P.SKU = S.SKU
>> and LEN(S.Name) =>> (select max(len(name))
>> from Product as X
>> where P.SKU = X.SKU))
>> Untested, of course, as is my version of the DELETE:
>> DELETE Product
>> WHERE EXISTS
>> (select * from Product as Z
>> where Product.SKU = Z.SKU
>> and Product.Name <> Z.Name
>> and LEN(Product.Name) < LEN(Z.Name))
>>
>>This worked wonderfully. Thank you, my friend.|||On Wed, 26 Jul 2006 11:47:07 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>What would happen if there were 2 rows with the same SKU and the same length
>[Name]?
>Would they BOTH be left -or deleted, and without indication of an incomplete
>update?
If they were both the longest they would both be left by the DELETE I
wrote, but only one of them would have appeared in the first query.
Roy

Complex Duplicate Row Issue (Need To Keep Shorter Value)

a client of mine caused a bit of an issue with one of our SQL Server
databases. They modified the database to create duplicate SKU values.
Each SKU value has a different Name column value.
What I need to do is delete the shorter value.
SKU Name
111 Glass Vase
111 Glass
333 Apple
so I need to get rid of SKU 111 w/ the Name value of Glass.
What I have done is created a view that give me a listing of all the
row values with duplicate SKUs, but I am not sure how to do the Len
comparison and return the shorter value.
Any help is appreciated.
Thanks!a query like:
select p.SKU, p.Name
from Products P
inner join (Select SKU, len(Name) as L from Products group by SKU) P2
on P.sku = p1.sku and len(p.Name) = p2.L
but this query keep duplicated values or names with the same length.
so you can do this:
select p.SKU, min(p.Name) as Name
from Products P
inner join (Select SKU, len(Name) as L from Products group by SKU) P2
on P.sku = p1.sku and len(p.Name) = p2.L
group by p.SKU
<carpeaqua@.gmail.com> wrote in message
news:1153932460.568433.293330@.s13g2000cwa.googlegroups.com...
>a client of mine caused a bit of an issue with one of our SQL Server
> databases. They modified the database to create duplicate SKU values.
> Each SKU value has a different Name column value.
> What I need to do is delete the shorter value.
> SKU Name
> 111 Glass Vase
> 111 Glass
> 333 Apple
> so I need to get rid of SKU 111 w/ the Name value of Glass.
> What I have done is created a view that give me a listing of all the
> row values with duplicate SKUs, but I am not sure how to do the Len
> comparison and return the shorter value.
> Any help is appreciated.
> Thanks!
>|||J=E9j=E9 wrote:

> select p.SKU, min(p.Name) as Name
> from Products P
> inner join (Select SKU, len(Name) as L from Products group by SKU) P2
> on P.sku =3D p1.sku and len(p.Name) =3D p2.L
> group by p.SKU
>
I had to modify it to be like this (it was giving a group by error)
select p.SKU, min(p.Name) as Name
from dbo.Product P
inner join (Select SKU, len(Name) as L from dbo.Product group by SKU,
Name) P2
on P.sku =3D p.sku and len(p.Name) =3D p2.L
group by p.SKU
It have let it run for about 7 minutes and its not outputting anything
as of yet. There are almost 7000 records in the table, so is that to
be expected? Apologies for my ignorance, but I am not used to working
with SQL Server. =20
Thanks!|||Seven minutes and 7000 rows, there is a problem.
Roy
On 26 Jul 2006 10:31:32 -0700, carpeaqua@.gmail.com wrote:

>Jj wrote:
>
>I had to modify it to be like this (it was giving a group by error)
>select p.SKU, min(p.Name) as Name
>from dbo.Product P
>inner join (Select SKU, len(Name) as L from dbo.Product group by SKU,
>Name) P2
>on P.sku = p.sku and len(p.Name) = p2.L
>group by p.SKU
>It have let it run for about 7 minutes and its not outputting anything
>as of yet. There are almost 7000 records in the table, so is that to
>be expected? Apologies for my ignorance, but I am not used to working
>with SQL Server.
>Thanks!|||I believe that this idea will work for the problem as you have described.
CREATE TABLE #MyTable
( RowID int IDENTITY
, SKU int
, Descript varchar(25)
)
SET NOCOUNT ON
INSERT INTO #MyTable VALUES ( 111, 'Glass Vase' )
INSERT INTO #MyTable VALUES ( 111, 'Glass' )
INSERT INTO #MyTable VALUES ( 333, 'Apple' )
DELETE #MyTable
WHERE Descript = ( SELECT min ( Descript )
FROM #MyTable
GROUP BY sku
HAVING count( SKU ) > 1
)
SELECT *
FROM #MyTable
DROP TABLE #MyTable
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<carpeaqua@.gmail.com> wrote in message news:1153932460.568433.293330@.s13g2000cwa.googlegroup
s.com...
>a client of mine caused a bit of an issue with one of our SQL Server
> databases. They modified the database to create duplicate SKU values.
> Each SKU value has a different Name column value.
>
> What I need to do is delete the shorter value.
>
> SKU Name
> 111 Glass Vase
> 111 Glass
> 333 Apple
>
> so I need to get rid of SKU 111 w/ the Name value of Glass.
>
> What I have done is created a view that give me a listing of all the
> row values with duplicate SKUs, but I am not sure how to do the Len
> comparison and return the shorter value.
>
> Any help is appreciated.
>
> Thanks!
>|||Yep, you are right. Drat, back to the drawing board...
(That wouldn't ever happen, would it?) ;-)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:7ebfc25dgml1a5dstv0a1retue37n3dcdu@.
4ax.com...[vbcol=seagreen]
> Arnie, if two SKUs have the same description that is going to erase
> some good data.
> Roy
> On Wed, 26 Jul 2006 10:58:36 -0700, "Arnie Rowland" <arnie@.1568.com>
> wrote:
>|||Here is my version of finding the rows you want to keep.
SELECT *
FROM Product as P
WHERE P.Name =
(select min(S.Name) from Product as S
where P.SKU = S.SKU
and LEN(S.Name) =
(select max(len(name))
from Product as X
where P.SKU = X.SKU))
Untested, of course, as is my version of the DELETE:
DELETE Product
WHERE EXISTS
(select * from Product as Z
where Product.SKU = Z.SKU
and Product.Name <> Z.Name
and LEN(Product.Name) < LEN(Z.Name))
Roy Harvey
Beacon Falls, CT|||Roy Harvey wrote:
> Here is my version of finding the rows you want to keep.
> SELECT *
> FROM Product as P
> WHERE P.Name =
> (select min(S.Name) from Product as S
> where P.SKU = S.SKU
> and LEN(S.Name) =
> (select max(len(name))
> from Product as X
> where P.SKU = X.SKU))
> Untested, of course, as is my version of the DELETE:
> DELETE Product
> WHERE EXISTS
> (select * from Product as Z
> where Product.SKU = Z.SKU
> and Product.Name <> Z.Name
> and LEN(Product.Name) < LEN(Z.Name))
This worked wonderfully. Thank you, my friend.|||Just to note that my two queries could involve different results. The
first one returned ONE row, even if the two rows had the same
(longest) length description. The DELETE only deleted rows with
shorter descriptions.
It would be worth double checking:
SELECT SKU, max(Name), min(Name), count(*)
FROM Product
GROUP BY SKU
HAVING COUNT(*) > 1
ORDER BY SKU, 2, 3
Roy
On 26 Jul 2006 11:28:48 -0700, carpeaqua@.gmail.com wrote:

>Roy Harvey wrote:
>
>This worked wonderfully. Thank you, my friend.|||What would happen if there were 2 rows with the same SKU and the same length
[Name]?
Would they BOTH be left -or deleted, and without indication of an incomplete
update?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:qkdfc2t451agl5stchnp57po3gur2vj7bk@.
4ax.com...[vbcol=seagreen]
> Just to note that my two queries could involve different results. The
> first one returned ONE row, even if the two rows had the same
> (longest) length description. The DELETE only deleted rows with
> shorter descriptions.
> It would be worth double checking:
> SELECT SKU, max(Name), min(Name), count(*)
> FROM Product
> GROUP BY SKU
> HAVING COUNT(*) > 1
> ORDER BY SKU, 2, 3
> Roy
> On 26 Jul 2006 11:28:48 -0700, carpeaqua@.gmail.com wrote:
>

Thursday, March 29, 2012

Concatenate strings in group column

Tuesday, March 27, 2012

Concat tables into one row in view

Computing several columns for each row in source table and joining to get result

Sunday, March 11, 2012

complicated Join - duplicate row problem

Thursday, March 8, 2012

complex tables - want to join to get one row of results from multiple rows

complex sql statement

Friday, February 24, 2012

Complex Duplicate Row Issue (Need To Keep Shorter Value)

Complex Duplicate Row Issue (Need To Keep Shorter Value)

Sql Comparing two tables

Blog Archive

About Me