One Account and one product result

As an example i have 4 columns:

Account, Product, Description, Price

I want to retrieve every account that has bought a particular product and sum up the price - I managed to do that but it lists it as:

Account, Product, Description, Price

Acc1 CD Compact Disc 50

Acc2 CD Compact Disc 50

Acc3 CD Compact Disc 50

Acc4 CD Compact Disc 50

So gives me a total for each account. I want to have the above returned back as ALL the accounts i have chosen but only list the product once with the price ie.:

Account, Product, Description, Price

Acc1,2,3,4 CD Compact Disc 200

How could this be done?

Thanks

Since you didn't provide the table DDL, I will work with only the information you supplied.

Code Snippet

SELECT
Account = substring(( SELECT ', ' + Account as [text()]
FROM MyTable m2
WHERE m2.Account = m1.Account
FOR XML path(''), elements
), 3, 1000
),
Product,
Description,
Price = sum( Price)
FROM MyTable m1
GROUP BY Product

|||

EssCee:

I am bothered by what I see in your result set:

Account Product Description Price

- -- -- --

Acc1,2,3,4 CD Compact Disc 200

What bothers me most is seing the ACCOUNT code listed as "Acc1,2,3,4". Are you really wanting the query to break the ACCOUNT column apart to get a "Base Account" component and then append a "string product" of all of the numeric pieces of all of the different values in the ACCOUNT column?

Also, I am baffeled by seeing the results of PRICE as the sum of the PRICE columns of each row. Would you also please verify if this is correct?

|||

Kent,

I didn't see your response as a 'rant', only asking deeper questions.

I didn't see the account grouping issue you raised -it wasn't apparant in the OP. The sum() issue seemed specious, but there are many, many 'strange' uses of data around. What gets me is how often we get requests to concatenate all column values into one result. I guess most folks just don't get it that presentation is a client task, not a data server task.

With the growth in SQL Server, and the apparant 'push' to move data code down to the application, we will be getting more and more 'half-backed' designs (EssCee, I don't know enough of your design to make that assumption, please don't take it personally.) Say after me: "Developers are not Database Professionals, They are trying to solve a user related problem. Their prime objective is to make the end user happy. A DBA's prime objective is to protect the data at all costs." Repeat as often as necessary -THEN bang you head on your desk.

|||

I know it doesn't answer your question directly...but....

Probably you could get by with a COUNT of the number of accounts that have purchased that product:

Code Snippet

select Count(*) as [AccountCount]

, Product

, Description

, sum(Price) as [GrossRevenue]

from <MyTable>

where Product = 'CD'

group by Product, Description

This will get you exactly what you asked for, but it's two hits to the table:

Code Snippet

declare @.AccountList nvarchar(4000)

select @.AccountList = isnull(@.AccountList + ',' + Account,Account)

from <MyTable>

where Product = 'CD'

select @.AccountList as [Accounts]

, Product

, Description

, sum(Price) as [GrossRevenue]

from <MyTable>

where Product = 'CD'

group by Product, Description

|||

Thanks everyone for your input. Just to clarify

The database is not designed by me. Another company who dont provide this support but im trying to develop a solution for some of these problems. Reason i need it in this format is because two companies have merged together and i need one total - hopefully that makes sense.

Ill try out all of these suggestions. Thanks to you all.

omitting null columns

Hey everyone, I haven't got any idea where to even start looking for a solution to this problem so any help would be appreciated. I'm trying to write a stored procedure that will return a table yet omit any columns that don't have anything written to them, so any columns that have no data will not be present in the table returned after the stored procedure.

Thanks in advance.

Hi Mitch,

Here you could take advantage of "IS NOT NULL" for example;

SELECT
*
FROM
MyTable
WHERE
ColumnName IS NOT NULL

|||Is there a way to do it more like

SELECT
*
FROM
MyTable
WHERE
AnyColumn IS NOT NULL

certain tables have different column names so I need the code to be flexible enough to omit any column it finds that is null in any table I give it.
|||Wait sorry that doesn't work, that tell it to select any rows that contain anything that is not null in 'x' column. I need it to select that row still but omit 'x' column so.

| A | B | C |
| 1 | | 2 |

becomes

| A | C |

| 1 | 2 ||||

just concatenate the columns, this assumes that the SET CONCAT_NULL_YIELDS_NULL is ON (which is the default)

Example
CREATE TABLE TestNulls (c1 INT,c2 INT, c3 INT,c4 INT,c5 INT, c6 INT)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (2,1,1,1,1,1)
INSERT TestNulls VALUES (3,1,1,1,1,1)
INSERT TestNulls VALUES (4,NULL,1,1,1,1)
INSERT TestNulls VALUES (5,1,1,1,1,1)
INSERT TestNulls VALUES (6,1,1,1,1,1)
INSERT TestNulls VALUES (7,1,1,NULL,1,1)

SELECT *
FROM TestNulls
WHERE C1+C2+C3+C4+C5+C6 IS NOT NULL

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||

Never mind the answer I gave you before, I didn't see this requirement

You should handle stuff like this client side not server side it is just adding unnecessary processing on the DB side

To do this on the DB you will have to use <evil>dynamic SQL</evil> dump the result in a temp table and build the SELECT statement dynamically by using a bunch of EXISTS...not pretty at all

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Menace is right: The dynamic SQL to accomplish what you want is pretty ugly. Here is an example of one way to do it:

create table dbo.example
( column_01 integer,
column_02 float,
column_03 integer,
column_04 varchar(30)
)
go

insert into example values (1, null, 2, 'This is a test.')
insert into example values (2, null, null, null)
insert into example values (3, null, 3, 'Tell me.')
go

declare @.execString varchar (6000)
declare @.columnString varchar (5000) set @.columnString = ''

select @.columnString = @.columnString + x.columnName + ','
from ( select q.colid,
cast (q.columnName as varchar(20)) as columnName
from ( select max ( case when column_01 is null then 0
else 1 end ) as column_01,
max ( case when column_02 is null then 0
else 2 end ) as column_02,
max ( case when column_03 is null then 0
else 3 end ) as column_03,
max ( case when column_04 is null then 0
else 4 end ) as column_04
from example
) p
unpivot
( colid for columnName in
( [column_01], [column_02], [column_03], [column_04] )
) q
group by q.colid, q.columnName
having q.colid <> 0
) x

select @.execString
= 'select '
+ left (@.columnString, len(@.columnString)-1)
+ ' from example '

exec ( @.execString )

-- -- Output: --

-- column_01 column_03 column_04
-- -- --
-- 1 2 This is a test.
-- 2 NULL NULL
-- 3 3 Tell me.

|||

Menace is right: The dynamic SQL to accomplish what you want is pretty ugly. Here is an example of one way to do it:

create table dbo.example
( column_01 integer,
column_02 float,
column_03 integer,
column_04 varchar(30)
)
go

insert into example values (1, null, 2, 'This is a test.')
insert into example values (2, null, null, null)
insert into example values (3, null, 3, 'Tell me.')
go

declare @.execString varchar (6000)
declare @.columnString varchar (5000) set @.columnString = ''

select @.columnString = @.columnString + x.columnName + ','
from ( select q.colid,
cast (q.columnName as varchar(20)) as columnName
from ( select max ( case when column_01 is null then 0
else 1 end ) as column_01,
max ( case when column_02 is null then 0
else 2 end ) as column_02,
max ( case when column_03 is null then 0
else 3 end ) as column_03,
max ( case when column_04 is null then 0
else 4 end ) as column_04
from example
) p
unpivot
( colid for columnName in
( [column_01], [column_02], [column_03], [column_04] )
) q
group by q.colid, q.columnName
having q.colid <> 0
) x

select @.execString
= 'select '
+ left (@.columnString, len(@.columnString)-1)
+ ' from example '

exec ( @.execString )

-- -- Output: --

-- column_01 column_03 column_04
-- -- --
-- 1 2 This is a test.
-- 2 NULL NULL
-- 3 3 Tell me.

|||

use below if you have the list of columns otherwise.. you'll have to query the syscolumn table.

select *
from mytable
where column1 is not null
and column2 is not null
and column3 ...

Mitch Wardrop wrote:

Is there a way to do it more like

SELECT
*
FROM
MyTable
WHERE
AnyColumn IS NOT NULL

certain tables have different column names so I need the code to be flexible enough to omit any column it finds that is null in any table I give it.

OLEDB source - Use table or select only columns needed.

Hi All,

With the OLEDB source, is it wrong to use a table / view as a source and only check the columns required or is it beneficial to write a select col1, col2 etc etc as a SQL command?

I cannot see any difference in performance between the two.

Thanks.

Always always always use a SQL command so as to avoid the situation documented here: http://blogs.conchango.com/jamiethomson/archive/2006/02/21/2930.aspx I can't stress this enough.

Also check #4 here: http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2554.aspx Basically, only pull in the data that you need otherwise performance will suffer.

-Jamie

|||Thanks Jamie but...

I would still like to know the underlying reason it is bad :)

Accepted that Select * is bad due to many reasons but:
My "tables" are views which themselves only select columns required for the data flow and nothing more.

Basically I am being lazy - I write the columns out in the view and don't _really_ want to write them again in SSIS :)

From a performance point of view, the above method is exactly the same either way. I cannot and have not seen what you described.

Will play around some more and try find a reason (unless someone wants to save us the trouble....)|||

Well if nothing else I would do it in the interests of best practice. And also cos I'm picky - I hate seeing a selected table rather than a SQL statement :)

-Jamie

|||

Jamie Thomson wrote:

Well if nothing else I would do it in the interests of best practice. And also cos I'm picky - I hate seeing a selected table rather than a SQL statement :)

-Jamie

A counter to that is I hate seeing any form of SQL in SSIS. Rather have the logic in a view / proc or just pull from the table. Make life easier when looking for bugs.
(yes, you could make a rule such as "do not use anything more that Select *" :)|||

Let's agree to disagree! :)

-J

OLEDB Bookmarks

I generated an ATL OLEDB Consumer with the VS 2005 wizard. The table is very simple and is located on my local SQL Server. It has two varchar columns and no keys or constraints. I can modify, insert and delete rows with no problem.

Now I want to use bookmarks. I added the following to the accessor class:

1. CBookmark<4> m_bookmark; // member variable

2. pPropSet->AddProperty(DBPROP_IRowsetLocate, true); // rowset property

3. BOOKMARK_ENTRY(m_bookmark) // column map entry

Now I can delete but I can no longer modify or insert. The error is DB_E_ERRORSOCCURED and the status of the two columns after a setdata or insert is DBSTATUS_E_UNAVAILABLE. This is the only error information available.

When I comment out the bookmark entry from the column map, I can modify, insert and delete again. The problem appears to be in how IRowsetChange handles bookmarks. I tried adding bookmarks to other tables using the above 3 steps and they also refuse to modify or insert with bookmarks enabled.

The bookmarks appear to be correct, as I can do a successful MoveToBookmark for any row in the table.

My environment: Windows XP SP2, VC++ 2005, SQL Server 2005 Express, WTL 8.0

Any ideas?

I guess you are doing the following when you modify:

Request IRowsetUpdate/IRowsetChange (get a rowset off that) and bind the columns (IColumnsInfo::GetColumnInfo) to create the accessor, you will see bookmark column. Detect this column and not bind it (use column flags to detect it) when you create the accessor. Or also try to set the status to DBSTATUS_S_IGNORE (so that when you update it will be ignored)

Hope this works

|||

Excellent suggestions, Raj! I created a new macro called bookmark_entry_status in atldbcli.h and in my application I set the status of the bookmark status variable to DBSTATUS_S_IGNORE. Now I can modify, insert and delete consumers that have bookmarks enabled. Here is the macro:

#define BOOKMARK_ENTRY_STATUS(variable, status) \

COLUMN_ENTRY_TYPE_STATUS(0, DBTYPE_BYTES, status, variable##.m_rgBuffer)

Thanks for the ideas. It set me on the proper path to the solution.

OLEDB Bookmarks

I generated an ATL OLEDB Consumer with the VS 2005 wizard. The table is very simple and is located on my local SQL Server. It has two varchar columns and no keys or constraints. I can modify, insert and delete rows with no problem.

Now I want to use bookmarks. I added the following to the accessor class:

1. CBookmark<4> m_bookmark; // member variable

2. pPropSet->AddProperty(DBPROP_IRowsetLocate, true); // rowset property

3. BOOKMARK_ENTRY(m_bookmark) // column map entry

Now I can delete but I can no longer modify or insert. The error is DB_E_ERRORSOCCURED and the status of the two columns after a setdata or insert is DBSTATUS_E_UNAVAILABLE. This is the only error information available.

When I comment out the bookmark entry from the column map, I can modify, insert and delete again. The problem appears to be in how IRowsetChange handles bookmarks. I tried adding bookmarks to other tables using the above 3 steps and they also refuse to modify or insert with bookmarks enabled.

The bookmarks appear to be correct, as I can do a successful MoveToBookmark for any row in the table.

My environment: Windows XP SP2, VC++ 2005, SQL Server 2005 Express, WTL 8.0

Any ideas?

I guess you are doing the following when you modify:

Request IRowsetUpdate/IRowsetChange (get a rowset off that) and bind the columns (IColumnsInfo::GetColumnInfo) to create the accessor, you will see bookmark column. Detect this column and not bind it (use column flags to detect it) when you create the accessor. Or also try to set the status to DBSTATUS_S_IGNORE (so that when you update it will be ignored)

Hope this works

|||

Excellent suggestions, Raj! I created a new macro called bookmark_entry_status in atldbcli.h and in my application I set the status of the bookmark status variable to DBSTATUS_S_IGNORE. Now I can modify, insert and delete consumers that have bookmarks enabled. Here is the macro:

#define BOOKMARK_ENTRY_STATUS(variable, status) \

COLUMN_ENTRY_TYPE_STATUS(0, DBTYPE_BYTES, status, variable##.m_rgBuffer)

Thanks for the ideas. It set me on the proper path to the solution.

OLEDB Bookmarks

I generated an ATL OLEDB Consumer with the VS 2005 wizard. The table is very simple and is located on my local SQL Server. It has two varchar columns and no keys or constraints. I can modify, insert and delete rows with no problem.

Now I want to use bookmarks. I added the following to the accessor class:

1. CBookmark<4> m_bookmark; // member variable

2. pPropSet->AddProperty(DBPROP_IRowsetLocate, true); // rowset property

3. BOOKMARK_ENTRY(m_bookmark) // column map entry

Now I can delete but I can no longer modify or insert. The error is DB_E_ERRORSOCCURED and the status of the two columns after a setdata or insert is DBSTATUS_E_UNAVAILABLE. This is the only error information available.

When I comment out the bookmark entry from the column map, I can modify, insert and delete again. The problem appears to be in how IRowsetChange handles bookmarks. I tried adding bookmarks to other tables using the above 3 steps and they also refuse to modify or insert with bookmarks enabled.

The bookmarks appear to be correct, as I can do a successful MoveToBookmark for any row in the table.

My environment: Windows XP SP2, VC++ 2005, SQL Server 2005 Express, WTL 8.0

Any ideas?

I guess you are doing the following when you modify:

Request IRowsetUpdate/IRowsetChange (get a rowset off that) and bind the columns (IColumnsInfo::GetColumnInfo) to create the accessor, you will see bookmark column. Detect this column and not bind it (use column flags to detect it) when you create the accessor. Or also try to set the status to DBSTATUS_S_IGNORE (so that when you update it will be ignored)

Hope this works

|||

Excellent suggestions, Raj! I created a new macro called bookmark_entry_status in atldbcli.h and in my application I set the status of the bookmark status variable to DBSTATUS_S_IGNORE. Now I can modify, insert and delete consumers that have bookmarks enabled. Here is the macro:

#define BOOKMARK_ENTRY_STATUS(variable, status) \

COLUMN_ENTRY_TYPE_STATUS(0, DBTYPE_BYTES, status, variable##.m_rgBuffer)

Thanks for the ideas. It set me on the proper path to the solution.

OLE DB Source Error Output

In the Input and Output Properties tab under Advance Editor for OLE DB Source, I cannot remove columns. I copied this Source from a standard template and have made the normal changes to make it work. However I keep getting this error...

Error: 0xC020837B at Load Server Security, OLE DB Source [1]: The output column "DBName" (1632) on the error output has no corresponding output column on the non-error output.

Error: 0xC004706B at Load Server Security, DTS.Pipeline: "component "OLE DB Source" (1)" failed validation and returned validation status "VS_ISBROKEN".

DBName of course is one of the columns that no longer exist, but I can't remove. Whenever I try to remove one of the columns, I get this error...

Error at Load Server Security [OLE DB Source[1]]: The column cannot be deleted. The component does not allow columns to be deleted from this input or output. Is there anything that I can do to remove the columns? Is there just a simple setting that I can change to make this work?
If you just double click on the OLE DB source, you can choose "Columns" on the left hand side and unselect the columns you do not want in the data flow.|||There is a problem with that though because I can't make any changes within the source. I always get the error...

Invalid object name 'tempdb.dbo.Server_Roles. This is because the table isn't created yet. It gets created when the connection manager connects to each server. It is a temporary table. Thus, it won't let me make any changes that I can't make in advance editor.

-Kyle
|||

Why don't you create the table somewhere else, get it configured properly, then change the table name afterward?

|||

That doesn't work. Currently I created a table in the temp directory and pointed the connection manager towards that DB and server. I still can't click on column and I can't change anything unless I hit cancel. My guess is that it may not be connecting to the server and DB correctly. I don't get any explanation for the error, its just one of the H errors.

-Kyle

|||I guess I don't understand what you are trying to do. Please explain further with more detail.

OLE DB Command transform and Output columns.

Hi All,

I have an OLE DB transform with a SQL Command of:

sp_get_sponsor_parent ?,? OUTPUT

where sp_get_sponsor_parent is defined like:

CREATE PROCEDURE [dbo].[sp_get_sponsor_parent]

@.pEID int,

@.results int OUTPUT

AS

BEGIN

.

.

.

END

I map the columns, refresh & OK out of the component without trouble, but on executing the package it fails during validation on this component. I'm utterly stumped.

Any light shed would be greatly appreciated.

Many thanks in advance,

Tamim.

I don't see any 'EXEC' in your sql commnad...may this be the problem?|||

This wasn't the problem Rafael - the EXEC is optional.

I resolved the issue however, by trialing just this one thing - outputting to a (derived) column within OLE DB Command - in a new/clean package. To that end I would like to bring the following example to everyone's attention: it's concise, comprehensive and clear, and thus can be considered canonical. No doubt there are other such examples out there, but this particular one helped me to push forwards, and thus deserves the publicity:

http://wiki.sqlis.com/default.aspx/SQLISWiki/OLEDBCommandTransformationAndIdentityColumns.html?diff=y

Thanks very much for your input Rafael.

Cheers,

Tamim.