Help with combining data from multiple rows into one column in a view

Hi, I am stumped and was hoping someone could help me out. Any help is
appreciated.

I have a view that looks sort of like this (but with a lot more
entries of course)

UniqueIdentifyierColumn1Column2
1 9999 100
2 9999 200
3 9999 300

What I want to do is to add a column to the view that will contain a
list of the values from column 2 where column 1 is the same.

UniqueIdentifyierColumn1Column2Column3
1 9999100100, 200, 300
2 9999200 100, 200, 300
3 9999300100, 200, 300(gaholmes@.comcast.net) writes:

Quote:

Originally Posted by

Hi, I am stumped and was hoping someone could help me out. Any help is
appreciated.
>
>
I have a view that looks sort of like this (but with a lot more
entries of course)
>
>
UniqueIdentifyier Column1 Column2
1 9999 100
2 9999 200
3 9999 300
>
>
What I want to do is to add a column to the view that will contain a
list of the values from column 2 where column 1 is the same.
>
UniqueIdentifyier Column1 Column2 Column3
1 9999 100 100, 200, 300
2 9999 200 100, 200, 300
3 9999 300 100, 200, 300

This may not be doable in a view, particularly if you are on SQL 2000.

SQL Server MVP Anith Sen has a collection of methods to attack this
problem at 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.mspx

help with arithmetic overflow error with insted of update trigger

Hi, I have a view set up with an INSTEAD OF UPDATE trigger specified.
When I perform an update on certain records, i.e.:
UPDATE mytableview
SET field1 = 1
WHERE userid = 1234
I am finding that *some* user id's result in the follwing error:
"Arithmetic overflow error converting expression to data type smalldatetime.
The statement has been terminated."
mytable view has a number of data fileds. All the data fields are of type
smalldatatime.
When i compare the user record of a userid that causes an error to one that
doesnt cause an error, the dates do vary, where some date fields have NULL's
or correctly formated smalldatatime values (yes I know about the restriction
of smalldatetime to range between 1900 and 2079).
The odd thing is that even if i am updating a non-date field within the
view, the above arithmetic error still occurs.
My trigger looks like the following:
CREATE TRIGGER mytrigger ON mytableview
INSTEAD OF UPDATE
AS
DECLARE @.mydate datetime
SELECT @.mydate = GETDATE()
UPDATE mytable SET
field1 = ISNULL(inserted.field1, 0),
field2 = ISNULL(inserted.field2, 0),
field3 = ISNULL(inserted.field3, 0),
date1 = inserted.date1,
date2 = @.date+30
FROM inserted
WHERE mytable.userid = inserted.userid
Am i getting this error because i am mixing a date2 fiels (which is of type
smalldatetime) with @.date (which is of type datetime) ?
Any help most appreciated.Do you need the extra ms or time range? If not try:
DECLARE @.mydate smalldatetime
SELECT @.mydate = GETDATE()
SELECT @.mydate
HTH
Jerry
"PWalker" <pwalker@.nospam.com> wrote in message
news:uCQYjxz0FHA.3256@.TK2MSFTNGP09.phx.gbl...
> Hi, I have a view set up with an INSTEAD OF UPDATE trigger specified.
> When I perform an update on certain records, i.e.:
> UPDATE mytableview
> SET field1 = 1
> WHERE userid = 1234
> I am finding that *some* user id's result in the follwing error:
> "Arithmetic overflow error converting expression to data type
> smalldatetime.
> The statement has been terminated."
> mytable view has a number of data fileds. All the data fields are of type
> smalldatatime.
> When i compare the user record of a userid that causes an error to one
> that doesnt cause an error, the dates do vary, where some date fields have
> NULL's or correctly formated smalldatatime values (yes I know about the
> restriction of smalldatetime to range between 1900 and 2079).
> The odd thing is that even if i am updating a non-date field within the
> view, the above arithmetic error still occurs.
> My trigger looks like the following:
> --
> CREATE TRIGGER mytrigger ON mytableview
> INSTEAD OF UPDATE
> AS
> DECLARE @.mydate datetime
> SELECT @.mydate = GETDATE()
> UPDATE mytable SET
> field1 = ISNULL(inserted.field1, 0),
> field2 = ISNULL(inserted.field2, 0),
> field3 = ISNULL(inserted.field3, 0),
> date1 = inserted.date1,
> date2 = @.date+30
> FROM inserted
> WHERE mytable.userid = inserted.userid
> --
> Am i getting this error because i am mixing a date2 fiels (which is of
> type smalldatetime) with @.date (which is of type datetime) ?
> Any help most appreciated.
>|||Also, drop the SELECT @.mydate -- was just for testing. Basically using the
SMALLDATETIME data type instead of DATETIME.
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23tFJV0z0FHA.2312@.TK2MSFTNGP14.phx.gbl...
> Do you need the extra ms or time range? If not try:
> DECLARE @.mydate smalldatetime
> SELECT @.mydate = GETDATE()
> SELECT @.mydate
> HTH
> Jerry
> "PWalker" <pwalker@.nospam.com> wrote in message
> news:uCQYjxz0FHA.3256@.TK2MSFTNGP09.phx.gbl...
>|||Sorry, I meant to say:
mytable view has a number of *date* fields. All the *date* fields are of
type
smalldatatime.
.. late night
cheers, peter
"PWalker" <pwalker@.nospam.com> wrote in message
news:uCQYjxz0FHA.3256@.TK2MSFTNGP09.phx.gbl...
> Hi, I have a view set up with an INSTEAD OF UPDATE trigger specified.
> When I perform an update on certain records, i.e.:
> UPDATE mytableview
> SET field1 = 1
> WHERE userid = 1234
> I am finding that *some* user id's result in the follwing error:
> "Arithmetic overflow error converting expression to data type
> smalldatetime.
> The statement has been terminated."
> mytable view has a number of data fileds. All the data fields are of type
> smalldatatime.
> When i compare the user record of a userid that causes an error to one
> that doesnt cause an error, the dates do vary, where some date fields have
> NULL's or correctly formated smalldatatime values (yes I know about the
> restriction of smalldatetime to range between 1900 and 2079).
> The odd thing is that even if i am updating a non-date field within the
> view, the above arithmetic error still occurs.
> My trigger looks like the following:
> --
> CREATE TRIGGER mytrigger ON mytableview
> INSTEAD OF UPDATE
> AS
> DECLARE @.mydate datetime
> SELECT @.mydate = GETDATE()
> UPDATE mytable SET
> field1 = ISNULL(inserted.field1, 0),
> field2 = ISNULL(inserted.field2, 0),
> field3 = ISNULL(inserted.field3, 0),
> date1 = inserted.date1,
> date2 = @.date+30
> FROM inserted
> WHERE mytable.userid = inserted.userid
> --
> Am i getting this error because i am mixing a date2 fiels (which is of
> type smalldatetime) with @.date (which is of type datetime) ?
> Any help most appreciated.
>|||thanks ill try that when i get to work
I hope its as obvious as changing smalldatetime to datetime!
cheers, peter

> Also, drop the SELECT @.mydate -- was just for testing. Basically using
> the SMALLDATETIME data type instead of DATETIME.
> HTH
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23tFJV0z0FHA.2312@.TK2MSFTNGP14.phx.gbl...
>

Help with AND and OR query

I am having trouble with the below query. This is attached to a SQLDataAdapter which in turn is connected to a grid view.

@.pram5 is a dropdownlist
all other perameters such as @.nw in the big OR statement are check boxes.

My tables look similar to this:

Company TblComodity TblRegion
---- ----- -----
PK CompanyID PK CommodityID PK RegionID
CompanyName FK CompanyID FK CompanyID
CommodityName North
South, East, etc

What I would am trying to do is have a user slect a commodity which is a distinct value from the comodity table. Then select by tick boxes locations, then in the grid view companies with possible locations and commoditys appear (matching record for commodity name and True values for any one particular location). My problem is even when I select a commodity and leave all tick boxes blank (false) the records still display for the selected commodity- like its only filltering on commodity name. Can anyone help ? I can provide more info if needed

Another little example of the above in case you dont understand.

Say in the dopdown list you choose "Buildings" and out of all the check box values you only choose Scotland, and North the record should still be returned if North is False and Scotland is True.

Here is my query:

SELECT TblCompany.CompanyID, TblCompany.CompanyName, TblRegion.NorthWest, TblRegion.NorthEast, TblRegion.SouthEast, TblRegion.SouthWest,
TblRegion.Scotland, TblRegion.Wales, TblRegion.Midlands, TblRegion.UKNational, TblRegion.EuropOotherThanUK, TblComodity.ComName
FROM TblCompany INNER JOIN
TblRegion ON TblCompany.CompanyID = TblRegion.CompanyID INNER JOIN
TblComodity ON TblCompany.CompanyID = TblComodity.CompanyID AND TblComodity.ComName = @.pram5
WHERE (TblRegion.NorthWest = @.nw) OR
(TblRegion.NorthEast = @.NE) OR
(TblRegion.SouthEast = @.se) OR
(TblRegion.SouthWest = @.sw) OR
(TblRegion.Scotland = @.scot) OR
(TblRegion.Wales = @.wal) OR
(TblRegion.Midlands = @.mid) OR
(TblRegion.EuropOotherThanUK = @.EU) AND (TblRegion.UKNational = @.UKN)

INNER JOIN evaluates if only the condition on both the tables is matched. In your case, you need to use OUTER JOIN (LEFT OR RIGHT) so that the records from left/right table are fetched even if the condition in right/left table fails. In your query change the INNER JOIN to LEFT OUTER JOIN

Thanks

|||

Thank you, I am new to this and use the query designer as I am learning. What you have said has taught me somthing new, We it be possible to modify my query to include your suggestion of the Left Outer Join?

Many thanks,

Adam.

|||

I re-read your question and looks like you have nothing to do with the OUTER JOINS. Try this

SELECT TblCompany.CompanyName, TblComodity.CommodityName, TblRegion.NorthWest
FROM TblRegion INNER JOIN
TblComodity ON TblRegion.CompanyID = TblComodity.CompanyID INNER JOIN
TblCompany ON TblComodity.CompanyID = TblCompany.CompanyID
WHERE TblComodity.CommodityName = @.pram5 AND TblRegion.NorthWest = @.nw

In the above query i used only one region. If you want to add more regions use AND

Thanks

|||

Hi,

Thats almost what I want but I need OR's for example if you have a Commodity Name called buildins and you select TRUE values for North East and South and the particular commodity only has a TRUE value for South I still would like the record returned. It would work similar to say a holiday web site where you would choose a country and say select multiple regions and all records for regions would be returned. Do you understand?

|||

Did you try using the query i provided with OR with which you can get the desired results.

Thanks

|||

Your code gives me all results for some strange reason if I make pram5 = 'buildings' and @.nw = 'true'

returns

Company Commodity Northwest

sampleCleaningTruePLCBuildingsFalseVictoriaBuildingsTrueVictoriaCateringTrueVictoriaComputingTrueVictoriaBuildingsTrueyiyuBuildingsFalse

SELECT TblCompany.CompanyName, TblComodity.ComName, TblRegion.NorthWest
FROM TblRegion INNER JOIN
TblComodity ON TblRegion.CompanyID = TblComodity.CompanyID INNER JOIN
TblCompany ON TblComodity.CompanyID = TblCompany.CompanyID
WHERE (TblComodity.ComName = @.pram5) OR
(TblRegion.NorthWest = @.nw)

sql

Help with an SQL view problem

Hi,

Have hit upon a problem with an SQL view, and wondered if anyone else had come upon a similar problem.

I have 2 tables in my database:

Categories

Requests

The Categories table contains three fields: CategoryID, ParentID, CategoryName

My Categories table has 3 entries:

CategoryID: 1
ParentID: Null
Title: Category 1

CategoryID: 2
ParentID: 1
Title: Category 2

CategoryID: 3
ParentID: 2
Title: Category 3

The Reqests table contains the following: RequestID, CategoryID, Title etc

My Requests table also has 3 entries:

RequestID: 1
CategoryID: 1
Title: Request 1

RequestID: 2
CategoryID: 2
Title: Request 2

RequestID: 3
CategoryID: 3
Title: Request 3

The problem I am faced with is that I need to build up a select statement which can return all requests filtered by the category ID, but this also needs to include any requests whose sub categories are a sub of the master category (if that makes sense).

Am not sure how I could go about this, I'm not sure what I would need to do to be able to loop up through all the parent ID's until I reach a null value?

Any help on this would be much appreciated.

Matt

Its good that you provided sample data. Can you also provide expected output for a given categoryID of 1 or 2 so we can understand your requirement better?

|||

Here is a query to get you a listing of all top level parents and one child.

select c.CategoryID ParentCategoryID, c.Title ParentTitle , r.RequestID ParentRequestID, r.Title , cc.CategoryID ChildCategoryID, cc.CategoryTitle ChildTitle , cc.RequestID ChildRequestID, cc.ChildRequestTitlefrom Categories cleftouter join (select cx.CategoryID, cx.ParentID, cx.Title CategoryTitle , rx.RequestID, rx.Title RequestTitlefrom Categories cxleftouter join Requests rxon cx.CategoryID = rx.CategoryIDand cx.ParentIDISNOT NULL )as ccon cc.ParentID = c.CategoryIDleftouter join Requests ron c.CategoryID = r.CategoryIDwhere c.ParentIDISNULL

But I do believe that this only gets you part of the way.

|||

Hi,

This article should provide you with some helpful guidance.

http://mssqltips.com/tip.asp?tip=938

|||

Thanks for all of your help, had a look at the recursive common table expressions in SQL server 2005 and looks like I now have my solution.

Cheers,

Matt

|||

Hi,

Managed to get around my first problem using the recursive common table expressions but have now hit upon another as I want to add paging using the Row_Number and custom sorting.

Here is what I have in my stored procedure so far:

DECLARE @.sqlString nvarchar(4000)

IF @.SortExpression = ''
BEGIN
SET @.SortExpression = 'DateCreated DESC'
END

IF @.CategoryID = 0
BEGIN
WITH AllJobRequests AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY @.SortExpression) AS RowNum FROM dbo.JobRequests
)
SELECT * FROM AllJobRequests WHERE RowNum BETWEEN @.Start AND @.End ORDER BY RowNum ASC
END
ELSE
BEGIN
SET @.sqlString = 'WITH GetSubCategories (JobRequestID, CategoryID, ParentID, CategoryName, Depth, RowNum) AS
(
SELECT dbo.JobRequests.JobRequestID, dbo.JobRequests.CategoryID, dbo.Categories.ParentID, dbo.Categories.CategoryName, 0 AS Depth, ROW_NUMBER() OVER (ORDER BY dbo.JobRequests.' + @.SortExpression + ') AS RowNum
FROM dbo.Categories INNER JOIN
dbo.JobRequests ON dbo.Categories.CategoryID = dbo.JobRequests.CategoryID
WHERE dbo.Categories.CategoryID = ' + CONVERT(nvarchar(10), @.CategoryID) + '
UNION ALL
SELECT dbo.JobRequests.JobRequestID, dbo.JobRequests.CategoryID, dbo.Categories.ParentID, dbo.Categories.CategoryName, GetSubCategories.Depth + 1 AS Depth, ROW_NUMBER() OVER (ORDER BY dbo.JobRequests.' + @.SortExpression + ') AS RowNum
FROM dbo.Categories INNER JOIN
dbo.JobRequests ON dbo.Categories.CategoryID = dbo.JobRequests.CategoryID
JOIN GetSubCategories ON Categories.ParentID = GetSubCategories.CategoryID
)
SELECT DISTINCT * FROM GetSubCategories WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @.Start) + ' AND ' + CONVERT(nvarchar(10), @.End) + ' ORDER BY RowNum ASC'
EXEC sp_executesql @.sqlString

This works to an extent apart from the sorting, have a look at the following output gained (the last bold characters are the RowNum order)

2 13 12 Central Heating 1 1
7 12 NULL Plumbing 0 1
6 12 NULL Plumbing 0 2
5 12 NULL Plumbing 0 3
4 12 NULL Plumbing 0 4
3 12 NULL Plumbing 0 5

The results are good in that they return both the output for the master category "Plumbing" and then the child category "Central Heating", but what would I now need to do to change this stored procedure to ensure I order the columns correctly?

Thanks,

Matt

HELP WITH A VIEW - calculated column

I need a view that contains a select statement that reads through all rows in a table, and based on the value in one of the columns, returns an additional column containing either "Manager" or "employee" depending on the values of that column. I'm not sure whenter to use a loop statement , a local variable, etc - -- but the end result must be a datagrid holding all all rows in the table plus the additional "Manager column" Can someone help me?

SELECT CASE col1 WHEN 'M' THEN 'Manager' ELSE 'Employee' END as EmpType
FROM tablename

You can use a CASE statement. Books online has a very good reference for using CASE.|||Thanks - works great!

Help with a View

I have a table tblElectronic that recieves data electronically. It
contains fields: filenumber, status, date, qualifier and comments. The
qualifier field is either 100 or 101, meaning the file is confirmed or
estimated, respectively. I then join tblElectronic to another table
(tblFile) in a view. I am having trouble building the view. The field
Qualifier in tblElectronic can have the value 100 or 101.

filenumber statusDate Qualifier Comments
1111xxxx01/01/01 100Comments
1111xxxx01/01/01 101Comments
1112xxxy01/01/01 101Comments
1113xyxy01/01/01 100 Comments

I want to use the record where qualifier = 100 in my view, except in the
case where 101 is the only qualifier that exists, meaning it has not
been confirmed yet.

So my view should pull the rows:
filenumberstatusdate qualifiercomments
1111xxxx01/01/01 101Comments
1112xxxy01/01/01 101Comments
1113xyxy01/01/01 100 Comments

I have tried case statements but to no avail. Here is my view:

SELECT tblFile.Filenumber, tblFile.DataofFile, tblElectronic.status,
tblElectronic.date, tblElectronic.comments
FROM tblFile inner join
tblElectronic on tblFile.filenumber = tblElectronic.filenumber
where tblElectronic.qualifier = ??

Not sure what to put here, since I want it to be where qualifier = 100
unless that doesn't exist, in which case I want it to be where qualifier
= 101.

Thanks for the help.

Rubia

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Hi

It is always better to post DDL ( CREATE TABLE statements etc..) and example
data (as INSERT statements) with the expected output from that data. This
will remove any ambiguities from your descriptions and helps everyone try
out their replies. There is no definition for tblFile which does not help!!

It is not clear if you want one or all records. But this may help

SELECT F.Filenumber, F.DataofFile, E.status, E.date, E.comments, E.qualifier
FROM tblFile F
join tblElectronic E on F.filenumber = E.filenumber
WHERE E.qualifier = 100
OR ( E.qualifier = 101
AND NOT EXISTS ( SELECT 1 FROM tblElectronic T
WHERE T.filenumber = E.filenumber
AND T.qualifier = 100 )
)

John

"Rubia 078" <rubia078@.yahoo.com> wrote in message
news:40ec7b0c$0$16462$c397aba@.news.newsgroups.ws.. .
> I have a table tblElectronic that recieves data electronically. It
> contains fields: filenumber, status, date, qualifier and comments. The
> qualifier field is either 100 or 101, meaning the file is confirmed or
> estimated, respectively. I then join tblElectronic to another table
> (tblFile) in a view. I am having trouble building the view. The field
> Qualifier in tblElectronic can have the value 100 or 101.
> filenumber status Date Qualifier Comments
> 1111 xxxx 01/01/01 100 Comments
> 1111 xxxx 01/01/01 101 Comments
> 1112 xxxy 01/01/01 101 Comments
> 1113 xyxy 01/01/01 100 Comments
> I want to use the record where qualifier = 100 in my view, except in the
> case where 101 is the only qualifier that exists, meaning it has not
> been confirmed yet.
> So my view should pull the rows:
> filenumber status date qualifier comments
> 1111 xxxx 01/01/01 101 Comments
> 1112 xxxy 01/01/01 101 Comments
> 1113 xyxy 01/01/01 100 Comments
>
> I have tried case statements but to no avail. Here is my view:
> SELECT tblFile.Filenumber, tblFile.DataofFile, tblElectronic.status,
> tblElectronic.date, tblElectronic.comments
> FROM tblFile inner join
> tblElectronic on tblFile.filenumber = tblElectronic.filenumber
> where tblElectronic.qualifier = ??
> Not sure what to put here, since I want it to be where qualifier = 100
> unless that doesn't exist, in which case I want it to be where qualifier
> = 101.
> Thanks for the help.
> Rubia
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||John,

Thank you, I believe that will work. I won't be able to test it until
tomorrow, but it would appear to be what I needed.

Thank you!

Rubia

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Help with a SQL query

I am selecting search results from the following view. The problem I have is, t5 (a junction) is returning multiple rows, which makes each search result show up several times. I just need one uniqe row from t5 ... (it has to be one row from just t5, not one row from the whole query). Can someone show me how I would accomplish this?

If anymore information is needed, let me know and I will post here. Thanks!

SELECT t1.*, t2.doc_type_description AS doc_type_description, t3.file_id AS file_id, t3.file_path AS file_path, t3.active AS active,
t5.soq_id AS soq_id, t5.soq_name AS soq_name
FROM dbo.tblSOQDocuments t1 INNER JOIN
dbo.tblSOQDocTypes t2 ON t1.doc_type_id = t2.doc_type_id INNER JOIN
dbo.tblSOQFiles t3 ON t1.doc_id = t3.doc_id INNER JOIN
dbo.jctSOQDocument t4 ON t1.doc_id = t4.doc_id INNER JOIN
dbo.tblSOQs t5 ON t4.soq_id = t5.soq_id
ORDER BY t2.doc_type_description, t5.soq_name, t1.doc_nameUse sub-queries.

instead of doing:

INNER JOIN dbo.tblSOQs AS t5 ON t4.soq_id = t5.soq_id

do:

INNER JOIN (SELECT soq_id, MIN(soq_name) FROM dbo.tblSOQs GROUP BY soq_id) AS t5 ON t4.soq_id = t5.soq_id|||For a unique row from t5, use:

INNER JOIN (SELECT DISTINCT soq_id, soq_name FROM dbo.tblSOQs) AS t5 ON t4.soq_id = t5.soq_id|||For a unique row from t5, use:

INNER JOIN (SELECT DISTINCT soq_id, soq_name FROM dbo.tblSOQs) AS t5 ON t4.soq_id = t5.soq_id

That will potentially return multiple rows which is what the original poster wants to avoid|||"I just need one uniqe row from t5 ..."

He does not specify "One unique soq_id", which is why I asked for clarification. "MIN(soq_name)" seems kind of arbitrary to me. Why includ soq_name in the result set if it is going to exclude some values?

...but perhaps that is what he wants.|||When I select from this view, it will return several rows. Potentially 2 rows for each result (each of these two rows carries different file information). One result from this query can potentially be associated with several soq_id's, but I just want the first one for each result.

Clear as mud?|||Then do an EXISTS...no?

Do you need data from both tables or just the one?|||I need data from all the tables that I have joined here ... the relationship will always exist.|||Well either SELECT DISTINCT which would eliminate duplicates (I know...too easy) or you want 1 row, WHERE the keys are the same, but you will have different values for those keys.

YOU have to decided what to do with those values. They exists.

Either USE MAX or MIN, ect, or s series of temp tables.

Either way, it has to be handled.

And YOU have to make the decision.sql

Help with a SQL query

I'm trying to draft a SQL Query for the following:
A bookstore that sells books - when a user selects a book to view it's
details, he should see a section that says "People who bought this book also
bought...with a list of various titles" - this is a common feature of sites
such as Barnes & Noble and Amazon.
How does the SQL Query work to get this information extracted out of the
database.
I have a Products table (with fields related to each product - Product ID,
Product Title, Product Category)
I have an Orders table (with fields related to each order - Order ID,
Persons ID, ...)
I have an Order Details table (with fields related to each item in the
order - Order ID, Product ID)RB wrote:
> I'm trying to draft a SQL Query for the following:
> A bookstore that sells books - when a user selects a book to view it's
> details, he should see a section that says "People who bought this book also
> bought...with a list of various titles" - this is a common feature of sites
> such as Barnes & Noble and Amazon.
> How does the SQL Query work to get this information extracted out of the
> database.
> I have a Products table (with fields related to each product - Product ID,
> Product Title, Product Category)
> I have an Orders table (with fields related to each order - Order ID,
> Persons ID, ...)
> I have an Order Details table (with fields related to each item in the
> order - Order ID, Product ID)
>
Assuming that you have the productID from the webpage, which will be
represented as @.PRODUCTID.
select P.productID, P.title
from Products P, OrderDetails OD
where P.productID = OD.productID and OD.PersonID IN (
select O.personID
from OrderDetails OD, Orders O
where OD.productID = @.PRODUCTID AND OD.OrderID = O.OrderID
)
Without the exact DB schema this is the best I could come up.
You will need to tweak it a little because you will get repeats etc.
It should be straight forward.

Help with "Create View" statement and Eorror Message

Hi all,
I am trying to create a view with approx. 3000 columns... and got the following error message:

"CREATE VIEW failed because column 'HSEPRIN' in view 'MyTestView' exceeds the maximum of 1024 columns.

Is it mean the max number of columns for each table is 1024? I thought in SQL server the table can contain as much information as possible.
Anyone can help to answer my question?

Thank you in advance.As much information vertically, not horizontally.

Frankly, if you are trying to create a view with 3000 columns, the problem is in your design, not SQL Server's limitations!

Why are you doing this? Maybe somebody here can find a better approach for you to take.|||No, a single row in a table can only contain a bit short of 8 kilobytes. A given row in a result set (therefore in a view) can only contain 1024 columns, and there are some limitations on the 1024th column.

That said, how on earth would you make use of a view that wide ?!?! What would you do with it ?

-PatP|||I definitely recommend printing it on legal-size paper set to landscape orientation, using Arial Narrow font.|||You can you go over the row limit in a physical table but you just get some warning about inserts and updates. See it often in poor designs or lack there of.|||Are you by any chance trying to crosstab that 40-year history you were talking about in that other post? That's the only thing I can think of that would give you that many columns. :D|||I do not have to see/create all the columns. However I would like to know the limitation. I just tried running the same query again for 950 columns which was succussful.
Maybe I have to run 3000 columns separately to create 3000/950 views. Can I union them together as a one object/something? In addition,
How am I going to update number of views on daily basis? :confused:|||All kidding aside.

Why don't you provide us with some more information about what exactly you are trying to do? Of course the ddl might be too much 411. But if you give us enough info one of might come up with something or at least some advice.

Some of the folks in this forum are as smart as they think they are. Myself excluded. I am as dumb as I seem. darrrrrrrrrrrrrrrrrrrrrrr!!!!!!!!!!|||This post is related to the one "updating daily information in a history table (was "Help-Brainstorming")"... which provides details.

Sorry about the confusion. And thank you for the help.|||Good Morning All,
Hope you all had a great weekend!
I think I am probably asking a silly question but...
I created a view by using the following statement:

create view TestView1
as
select date as Date,
XXXXX= sum(case when ID='XXXXX' then Field1 else 0 end),
YYYYY= sum(case when ID='YYYYY' then Field1 else 0 end)
from MyTable
Group by Date

The structure of MyTable is:
Date(datetime) ID(char 10) Field1(float)
1/1/65 XXXXX -999.999
1/4/65 XXXXX -999.999
...
2/24/05 XXXXX 500
2/25/05 XXXXX 550
1/1/65 YYYYY -999.999
1/4/65 YYYYY -999.999
...
2/24/05 YYYYY 600
2/25/05 YYYYY 650

when I run "select * from TestView order by date"
The actual results I got:
Date XXXXX YYYYY
1/1/65 0.0 0.0
1/4/65 0.0 0.0
...
2/24/05 500 600
2/25/05 550 650

This is the results I should expect:
Date XXXXX YYYYY
1/1/65 -999.999 -999.999
1/4/65 -999.999 -999.999
...
2/24/05 500 600
2/25/05 550 650

What is wrong with my create view statement? Do I have to specify the datatype?

Thank you for the help in advance.|||What is wrong with it?

The question is, "What good is it?"

What is the practical use of a view with 3000 cross-tabbed columns? You can't print it. You can't display it. You can't use it practically in any other views or procedures.

What are you planning to do with this?|||This is going to be the data source for another application (Matlab).
And this is desired format. It does not matter if I can display them all as long as I can display partially to make sure the information is there and the expected data format.

Should I put information into a table instead of View?

Thank you for the help!|||MatLab can't accept normalized data?

And it can't do its own crosstabs?

That is pretty weak.

I'm sorry, but I just can't suggest any solution along the lines you are thinking, because I think it is going to cause you severe problems in the future.|||Matlab is a statistic package to do math calculation and generate graphs. It may take 5+ hours to run the results therefore I am thinking to use SQL to generate the expected data source format to feed into Matlab.
If you think the only possible solution should be on Matlab side, I guess I have to work on that.
However, do you know why my actual result from my sql statement shows data as "0.0" instead of expected "-999.999" which is stored in the table?

Any suggestion and comments are truely appreciated!
shiparsons|||I don't have that problem..you probably shouldn't be using float though...

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99([ID] varchar(15), [Date] datetime, Field1 float)
GO

INSERT INTO myTable99([Date],[ID],Field1)
SELECT '1/1/65', 'XXXXX', -999.999 UNION ALL
SELECT '1/4/65', 'XXXXX', -999.999 UNION ALL
SELECT '2/24/05', 'XXXXX', 500 UNION ALL
SELECT '2/25/05', 'XXXXX', 550 UNION ALL
SELECT '1/1/65', 'YYYYY', -999.999 UNION ALL
SELECT '1/4/65', 'YYYYY', -999.999 UNION ALL
SELECT '2/24/05', 'YYYYY', 600 UNION ALL
SELECT '2/25/05', 'YYYYY', 650
GO

CREATE VIEW myView99
AS
SELECT [Date]
, SUM(CASE WHEN [ID]='XXXXX' THEN Field1 ELSE 0 END) AS X
, SUM(CASE WHEN [ID]='YYYYY' THEN Field1 ELSE 0 END) AS Y
FROM MyTable99
GROUP BY [Date]
GO

SELECT * FROM myView99
GO

SET NOCOUNT OFF
DROP VIEW myView99
DROP TABLE myTable99
GO|||Brett,
Thank you for the help! You are right. It works fine.
The problem was on my end. In my statement I had a space was quoted in for ID field. (ID=' XXXXX ' instead of ID='XXXXX')

:p

Help wanted msaccess PIVOT-query --> MS-SQL view/sp

Can someone help me parsing this ms-access PIVOT sql-statement to a
ms-sql-server sql-statement?
Many thanks in advance

TRANSFORM Count(KlantenStops.id) AS AantalVanid
SELECT KlantenStops.Uitvoerder, KlantenStops.Klant
FROM KlantenStops
GROUP BY KlantenStops.Uitvoerder, KlantenStops.Klant
PIVOT DatePart("m",leverdatum,1,0) In
("1","2","3","4","5","6","7","8","9","10","11","12");On Wed, 18 Jan 2006 19:49:49 +0100, Gert v O wrote:

>Can someone help me parsing this ms-access PIVOT sql-statement to a
>ms-sql-server sql-statement?
>Many thanks in advance
>TRANSFORM Count(KlantenStops.id) AS AantalVanid
>SELECT KlantenStops.Uitvoerder, KlantenStops.Klant
>FROM KlantenStops
>GROUP BY KlantenStops.Uitvoerder, KlantenStops.Klant
>PIVOT DatePart("m",leverdatum,1,0) In
>("1","2","3","4","5","6","7","8","9","10","11","12");

Hi Gert,

I'm not exactly sure how the Access PIVOT syntax works (including table
structure, sample data and expected results would have been a good idea;
check www.aspfaq.com/2006 for the best format to supply this info), but
the query below will do what I think the Access format does:

SELECT Uitvoerder, Klant,
COUNT(CASE WHEN DATEPART(month, leverdatum) = 1 THEN 'TelMe'
END) AS Januari,
COUNT(CASE WHEN DATEPART(month, leverdatum) = 2 THEN 'TelMe'
END) AS Februari,
....,
COUNT(CASE WHEN DATEPART(month, leverdatum) = 12 THEN 'TelMe'
END) AS December
FROM KlantenStops
GROUP BY Uitvoerder, Klant

If you're using SQL Server 2005, you can also use the new PIVOT syntax.
You'll have to check Books Online for the details, though, as I haven't
had a chance to play with the new syntax yet.

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis wrote:
> On Wed, 18 Jan 2006 19:49:49 +0100, Gert v O wrote:
>> Can someone help me parsing this ms-access PIVOT sql-statement to a
>> ms-sql-server sql-statement?
>> Many thanks in advance
>>
>> TRANSFORM Count(KlantenStops.id) AS AantalVanid
>> SELECT KlantenStops.Uitvoerder, KlantenStops.Klant
>> FROM KlantenStops
>> GROUP BY KlantenStops.Uitvoerder, KlantenStops.Klant
>> PIVOT DatePart("m",leverdatum,1,0) In
>> ("1","2","3","4","5","6","7","8","9","10","11","12");
>>
> Hi Gert,
> I'm not exactly sure how the Access PIVOT syntax works (including
> table structure, sample data and expected results would have been a
> good idea; check www.aspfaq.com/2006 for the best format to supply
> this info), but the query below will do what I think the Access
> format does:
> SELECT Uitvoerder, Klant,
> COUNT(CASE WHEN DATEPART(month, leverdatum) = 1 THEN 'TelMe'
> END) AS Januari,
> COUNT(CASE WHEN DATEPART(month, leverdatum) = 2 THEN 'TelMe'
> END) AS Februari,
> ....,
> COUNT(CASE WHEN DATEPART(month, leverdatum) = 12 THEN 'TelMe'
> END) AS December
> FROM KlantenStops
> GROUP BY Uitvoerder, Klant
> If you're using SQL Server 2005, you can also use the new PIVOT
> syntax. You'll have to check Books Online for the details, though, as
> I haven't had a chance to play with the new syntax yet.

Thanx Hugo|||I have a blog post on using dynamic SQL to pivot when you don't know how many columns there will be it's here http://www.daymap.net/blog

Help understanding generated MDX WHERE clause

When your report datasource is a cube, MDX is generated when you use the design view. In the MDX editor the generated MDX can be viewed. Using parameters I always get a where clause with code like the following:

IIF( STRTOSET(@.OrgLevelname, CONSTRAINED).Count = 1, STRTOSET(@.OrgLevelname, CONSTRAINED), [Organisation].[Level 2 name].currentmember )

I like to understand what is generated. Is there something I can read on the generated WHERE clause (I do understand the generated SELECT and FROM clauses)? Or can someone shed a light on it?

Why does the MDX need to branch on 'Count = 1' In what way does the result slice my data when Count = 1 or when Count <> 1?

Thanks,
Henk

Really no MDX guru around who can explain this to me?|||

Henk,

I would suggest posting your MDX question in the SQL Server Analysis Services forum at this url:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=83&SiteID=1

|||Thanks, I'll do that.

Help relation Between Xml and SQL2000

HI,
I have a xml file and would like to Make Relation Between
it and table Or View in SQl2000
can some one say how?
Thanks
You should look into using either OpenXML or the annotated schema. Check out
the SQL Server Books Online section on XML or
http://msdn.microsoft.com/sqlxml
Best regards
Michael
"Abdallah Salah" <a_elsabbahi2000@.yahoo.com> wrote in message
news:a8cb01c487d8$c1801960$a401280a@.phx.gbl...
> HI,
> I have a xml file and would like to Make Relation Between
> it and table Or View in SQl2000
> can some one say how?
>
> Thanks
>

Help reg Watch Window

hi frnds,

i want to view the runtime variable value in the watch window while the package is running,

how i will get that watch window. i searched all the menus but i didnt get it.

im using Microsoft Visual Studio 2005 for SSIS package.

To do this you, first need to be stopped on a breakpoint. The Watch window is only valid during a break point, so whilst it may be visible whilst running, the values are not updated in real-time.

So set a breakpoint. When stopped, got to the Variables window, click to select and then drag the variable to the watch window. The variables will be shown in the watch window with the current value. The variables will stay in the watch window for the duration of your working session, so you don't have to keep dragging them in everytime youi hit a breakpoint.