grouping and concatenating

i have a the following table:

CREATE TABLE [dbo].[tIndex] (
[indexID] [int] IDENTITY (1, 1) NOT NULL ,
[wordID] [int] NULL ,
[wordPos] [int] NULL ,
[paraID] [int] NULL
) ON [PRIMARY]
GO

for each wordID i have many paraID and for each wordID,paraID i have many wordPos

i will use the following convention:
paraID=p
wordID=w
wordPos=wp

i want to concatenate the columns to get the following format:

row1: w1 p1,NB1,wp1,wp2,w3... | p2,NB2,wp4,wp5,wp6... | ...
row2: w2 ...
row3: w3 ...

where NB1 is the number of wp having w1 and p1

Note: the length of a row may exceed 8000 charsOriginally posted by samham
Note: the length of a row may exceed 8000 chars If any column exceeds 8000 characters (which is what I think you are trying to say), then you have no choice... You must build those columns on the client.

-PatP|||the total rows of the table is 15 million

i am trying to copy the table content to a text file in the format i described

I put the note about the 8000 chars to say that 1 row cannot be contained in a varchar(8000) variable in case concatenating using a varchar(8000) is a solution

i am using c# as my programming language so my last option is to do this by c# code by selecting wordID and then for each rowID select the paraID and then for each wordID,paraID select the wordPos

but i was wondering if this can be done by sql and then send the result directly to a textfile|||Sorry, SQL Server can't do what you want. According to SQL Maximum Capacity Specifications (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp), the maximum row size for SQL Server is 8060 bytes. The maximum single string is 8000 bytes.

In your case, your last resort is the only one that might work.

-PatP|||Ok Pat thank you
i will just go for the c# solutionsql

Group by/ Having question

Hi all
I have these two tables, account and package and i want to get all
accounts that have more than 1 package where dateremoved is null.
This is what I have so far..
select accounts.name, packages.guid, packages.datecreated,
packages.dateremoved
from accounts inner join packages on packages.account = accounts.guid
where packages.datecreated > '2007-01-12 00:00:00'
and (packages.dateremoved is null)
order by packages.datecreated
This gives me number of accounts and i can see the accounts that have
more than 1 package where the dateremoved field is null but I only want
to get those accounts..
I did try this;
select accounts.guid, packages.guid, packages.datecreated
from accounts inner join packages on packages.account = accounts.guid
where packages.dateremoved is null
and (packages.dateremoved in ( select packages.dateremoved
from packages
GROUP BY (packages.dateremoved)
HAVING count(packages.dateremoved) > 1
but this gives me now results.. not error, just empty results.
Can you guys and girls help me with this'
thanxAccording to yout definition:
"I have these two tables, account and package and i want to get all
accounts that have more than 1 package where dateremoved is null. "
SELECT * FROM Accounts A
WHERE EXISTS
(
SELECT * FROM Package P
WHERE dateremoved IS NULL
AND P.account = A.guid
AND where packages.datecreated > '2007-01-12 00:00:00'
)
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||SELECT accounts.*
FROM accounts
JOIN (select account
from packages
where datecreated > '2007-01-12 00:00:00'
and dateremoved is null
group by account
HAVING COUNT(*) > 1) as X
ON accounts.guid = X.account
If all you wanted was the list of account numbers, just run the inner
query. If you also need other account information you need the rest
too.
Roy Harvey
Beacon Falls, CT
On 16 Jan 2007 03:32:59 -0800, kollatjorva@.gmail.com wrote:
>Hi all
>I have these two tables, account and package and i want to get all
>accounts that have more than 1 package where dateremoved is null.
>This is what I have so far..
>select accounts.name, packages.guid, packages.datecreated,
>packages.dateremoved
>from accounts inner join packages on packages.account = accounts.guid
>where packages.datecreated > '2007-01-12 00:00:00'
>and (packages.dateremoved is null)
>order by packages.datecreated
>This gives me number of accounts and i can see the accounts that have
>more than 1 package where the dateremoved field is null but I only want
>to get those accounts..
>I did try this;
>select accounts.guid, packages.guid, packages.datecreated
>from accounts inner join packages on packages.account = accounts.guid
>where packages.dateremoved is null
>and (packages.dateremoved in ( select packages.dateremoved
>from packages
>GROUP BY (packages.dateremoved)
>HAVING count(packages.dateremoved) > 1
>but this gives me now results.. not error, just empty results.
>Can you guys and girls help me with this'
>thanx|||This works like a charm..
Thanks Roy, regards to you from Iceland.
Roy Harvey wrote:
> SELECT accounts.*
> FROM accounts
> JOIN (select account
> from packages
> where datecreated > '2007-01-12 00:00:00'
> and dateremoved is null
> group by account
> HAVING COUNT(*) > 1) as X
> ON accounts.guid = X.account
> If all you wanted was the list of account numbers, just run the inner
> query. If you also need other account information you need the rest
> too.
> Roy Harvey
> Beacon Falls, CT
> On 16 Jan 2007 03:32:59 -0800, kollatjorva@.gmail.com wrote:
> >Hi all
> >I have these two tables, account and package and i want to get all
> >accounts that have more than 1 package where dateremoved is null.
> >
> >This is what I have so far..
> >
> >select accounts.name, packages.guid, packages.datecreated,
> >packages.dateremoved
> >from accounts inner join packages on packages.account = accounts.guid
> >where packages.datecreated > '2007-01-12 00:00:00'
> >and (packages.dateremoved is null)
> >order by packages.datecreated
> >
> >This gives me number of accounts and i can see the accounts that have
> >more than 1 package where the dateremoved field is null but I only want
> >to get those accounts..
> >
> >I did try this;
> >
> >select accounts.guid, packages.guid, packages.datecreated
> >from accounts inner join packages on packages.account = accounts.guid
> >where packages.dateremoved is null
> >and (packages.dateremoved in ( select packages.dateremoved
> >from packages
> >GROUP BY (packages.dateremoved)
> >HAVING count(packages.dateremoved) > 1
> >
> >but this gives me now results.. not error, just empty results.
> >
> >Can you guys and girls help me with this'
> >thanx|||You should consider using the plan analyzer, I guess Roys solution will
take longer than just using the EXISTS.
-Jens|||On 16 Jan 2007 06:29:40 -0800, "Jens" <Jens@.sqlserver2005.de> wrote:
>You should consider using the plan analyzer, I guess Roys solution will
>take longer than just using the EXISTS.
>-Jens
Recall that the requirement was "to get all accounts that have more
than 1 package where dateremoved is null." I believe the EXISTS
version posted does not test for more than one, it tests for at least
one.
Roy Harvey
Beacon Falls, CT|||You are right, I missed the "more" than one.

Group by/ Having question

Hi all
I have these two tables, account and package and i want to get all
accounts that have more than 1 package where dateremoved is null.
This is what I have so far..
select accounts.name, packages.guid, packages.datecreated,
packages.dateremoved
from accounts inner join packages on packages.account = accounts.guid
where packages.datecreated > '2007-01-12 00:00:00'
and (packages.dateremoved is null)
order by packages.datecreated
This gives me number of accounts and i can see the accounts that have
more than 1 package where the dateremoved field is null but I only want
to get those accounts..
I did try this;
select accounts.guid, packages.guid, packages.datecreated
from accounts inner join packages on packages.account = accounts.guid
where packages.dateremoved is null
and (packages.dateremoved in ( select packages.dateremoved
from packages
GROUP BY (packages.dateremoved)
HAVING count(packages.dateremoved) > 1
but this gives me now results.. not error, just empty results.
Can you guys and girls help me with this?
thanx
SELECT accounts.*
FROM accounts
JOIN (select account
from packages
where datecreated > '2007-01-12 00:00:00'
and dateremoved is null
group by account
HAVING COUNT(*) > 1) as X
ON accounts.guid = X.account
If all you wanted was the list of account numbers, just run the inner
query. If you also need other account information you need the rest
too.
Roy Harvey
Beacon Falls, CT
On 16 Jan 2007 03:32:59 -0800, kollatjorva@.gmail.com wrote:

>Hi all
>I have these two tables, account and package and i want to get all
>accounts that have more than 1 package where dateremoved is null.
>This is what I have so far..
>select accounts.name, packages.guid, packages.datecreated,
>packages.dateremoved
>from accounts inner join packages on packages.account = accounts.guid
>where packages.datecreated > '2007-01-12 00:00:00'
>and (packages.dateremoved is null)
>order by packages.datecreated
>This gives me number of accounts and i can see the accounts that have
>more than 1 package where the dateremoved field is null but I only want
>to get those accounts..
>I did try this;
>select accounts.guid, packages.guid, packages.datecreated
>from accounts inner join packages on packages.account = accounts.guid
>where packages.dateremoved is null
>and (packages.dateremoved in ( select packages.dateremoved
>from packages
>GROUP BY (packages.dateremoved)
>HAVING count(packages.dateremoved) > 1
>but this gives me now results.. not error, just empty results.
>Can you guys and girls help me with this?
>thanx
|||This works like a charm..
Thanks Roy, regards to you from Iceland.
Roy Harvey wrote:[vbcol=seagreen]
> SELECT accounts.*
> FROM accounts
> JOIN (select account
> from packages
> where datecreated > '2007-01-12 00:00:00'
> and dateremoved is null
> group by account
> HAVING COUNT(*) > 1) as X
> ON accounts.guid = X.account
> If all you wanted was the list of account numbers, just run the inner
> query. If you also need other account information you need the rest
> too.
> Roy Harvey
> Beacon Falls, CT
> On 16 Jan 2007 03:32:59 -0800, kollatjorva@.gmail.com wrote:
|||You should consider using the plan analyzer, I guess Roys solution will
take longer than just using the EXISTS.
-Jens
|||On 16 Jan 2007 06:29:40 -0800, "Jens" <Jens@.sqlserver2005.de> wrote:

>You should consider using the plan analyzer, I guess Roys solution will
>take longer than just using the EXISTS.
>-Jens
Recall that the requirement was "to get all accounts that have more
than 1 package where dateremoved is null." I believe the EXISTS
version posted does not test for more than one, it tests for at least
one.
Roy Harvey
Beacon Falls, CT
|||You are right, I missed the "more" than one.

Group by/ Having question

Hi all
I have these two tables, account and package and i want to get all
accounts that have more than 1 package where dateremoved is null.
This is what I have so far..
select accounts.name, packages.guid, packages.datecreated,
packages.dateremoved
from accounts inner join packages on packages.account = accounts.guid
where packages.datecreated > '2007-01-12 00:00:00'
and (packages.dateremoved is null)
order by packages.datecreated
This gives me number of accounts and i can see the accounts that have
more than 1 package where the dateremoved field is null but I only want
to get those accounts..
I did try this;
select accounts.guid, packages.guid, packages.datecreated
from accounts inner join packages on packages.account = accounts.guid
where packages.dateremoved is null
and (packages.dateremoved in ( select packages.dateremoved
from packages
GROUP BY (packages.dateremoved)
HAVING count(packages.dateremoved) > 1
but this gives me now results.. not error, just empty results.
Can you guys and girls help me with this'
thanxAccording to yout definition:
"I have these two tables, account and package and i want to get all
accounts that have more than 1 package where dateremoved is null. "
SELECT * FROM Accounts A
WHERE EXISTS
(
SELECT * FROM Package P
WHERE dateremoved IS NULL
AND P.account = A.guid
AND where packages.datecreated > '2007-01-12 00:00:00'
)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||SELECT accounts.*
FROM accounts
JOIN (select account
from packages
where datecreated > '2007-01-12 00:00:00'
and dateremoved is null
group by account
HAVING COUNT(*) > 1) as X
ON accounts.guid = X.account
If all you wanted was the list of account numbers, just run the inner
query. If you also need other account information you need the rest
too.
Roy Harvey
Beacon Falls, CT
On 16 Jan 2007 03:32:59 -0800, kollatjorva@.gmail.com wrote:

>Hi all
>I have these two tables, account and package and i want to get all
>accounts that have more than 1 package where dateremoved is null.
>This is what I have so far..
>select accounts.name, packages.guid, packages.datecreated,
>packages.dateremoved
>from accounts inner join packages on packages.account = accounts.guid
>where packages.datecreated > '2007-01-12 00:00:00'
>and (packages.dateremoved is null)
>order by packages.datecreated
>This gives me number of accounts and i can see the accounts that have
>more than 1 package where the dateremoved field is null but I only want
>to get those accounts..
>I did try this;
>select accounts.guid, packages.guid, packages.datecreated
>from accounts inner join packages on packages.account = accounts.guid
>where packages.dateremoved is null
>and (packages.dateremoved in ( select packages.dateremoved
>from packages
>GROUP BY (packages.dateremoved)
>HAVING count(packages.dateremoved) > 1
>but this gives me now results.. not error, just empty results.
>Can you guys and girls help me with this'
>thanx|||This works like a charm..
Thanks Roy, regards to you from Iceland.
Roy Harvey wrote:[vbcol=seagreen]
> SELECT accounts.*
> FROM accounts
> JOIN (select account
> from packages
> where datecreated > '2007-01-12 00:00:00'
> and dateremoved is null
> group by account
> HAVING COUNT(*) > 1) as X
> ON accounts.guid = X.account
> If all you wanted was the list of account numbers, just run the inner
> query. If you also need other account information you need the rest
> too.
> Roy Harvey
> Beacon Falls, CT
> On 16 Jan 2007 03:32:59 -0800, kollatjorva@.gmail.com wrote:
>|||You should consider using the plan analyzer, I guess Roys solution will
take longer than just using the EXISTS.
-Jens|||On 16 Jan 2007 06:29:40 -0800, "Jens" <Jens@.sqlserver2005.de> wrote:

>You should consider using the plan analyzer, I guess Roys solution will
>take longer than just using the EXISTS.
>-Jens
Recall that the requirement was "to get all accounts that have more
than 1 package where dateremoved is null." I believe the EXISTS
version posted does not test for more than one, it tests for at least
one.
Roy Harvey
Beacon Falls, CT|||You are right, I missed the "more" than one.

Group By Clause Limitation

Code is:
select
case when ItemCode is null then '-'
else ItemCode
End,
case when sum(RecdQty) is null then '-'
else sum(RecdQty)
End
from ItemMaster where ItemCode='V001' group by ItemCode

Problem Statement:
If query is not getting any records for above mentioned condition, then I want zero to be displayed if datatype is int (i.e. for sum(RecdQty) field) and '-' to be diplayed if datatype is varchar (i.e. for ItemCode field).
In this situation, "ItemCode is null" and "sum(RecdQty) is null" conditions are not been utilised.
Is this a limitation of case or group by clause?No, this is not a limitation of SQL at all, it is doing exactly what it is supposed to do. Please see my explanation from the last time you asked this question by clicking here (http://www.dbforums.com/showthread.php?p=6237156#post6237156). If that explanation isn't clear or sufficient, please continue the discussion in that thread instead of starting new threads.

-PatP|||Consider this

select count(*) from master..sysdatabases
where 1=2

will return 0. But

select count(*) from master..sysdatabases
where 1=2
group by status

return no records

Now you want a specific ItemCode. There is no need for the group by

select isnull(min(ItemCode),'-')
,isnull(sum(RecdQty),0)
from ItemMaster
where ItemCode='V001'

GROUP BY and populating temp table, ideas?

Hello All,
I have the following table, and want to create a report as seen below.
CREATE TABLE [dbo].[Sales] (
[ACTIVITY_ID] [varchar] (16) NOT NULL ,
[CREATED_BY] [varchar] (10) NULL,
[YEAR] [varchar] (9) NULL ,
[PERIOD] [varchar] (2) NULL ,
[WEEK] [char] (1) NULL ,
[AMOUNT] [varchar] (3) NULL
) ON [PRIMARY]
GO
I need to see count(*) of each rep for each w and current preriod.
Something like this:
REP_NAME W1 W2 W3 W4 W5 Period(Month)
======== ===== ===== ===== ===== ===== =============
DAVID 5 10 5 20
WILLIAM 2 8 5 15
JANE 10 2 10 22
Do I need to run seperate group by's for each w and populate a temp table
?
Or there can be a simpler way to do that?
Thanks,
Ada
--
SQL Server DBAFirst of all, pivoting data for presentation purposes does not belong on the
data layer.
But if you really, really, really need to do it in T-SQL read this:
http://www.windowsitpro.com/Article...15608.html?Ad=1
ML