Database

SELECT     Item_Name,<br />
<br />
                          (SELECT     SUM(Piece_No) AS Quantity FROM     Purchase_Detail  WHERE      (Product.Item_Name = Item)) AS QuantityB,<br />
<br />
                          (SELECT     SUM(Itm_Qty) AS Quantity  FROM     Detail_Description    WHERE      (Product.Item_Name = Item)) AS QuantityC,<br />
<br />
                          (SELECT     SUM(Itm_Qty) AS Quantity  FROM     Refund_Detail  WHERE      (Product.Item_Name = Item_Name)) AS QuantityD,<br />
<br />
                          (SELECT     SUM(Piece_No) AS Quantity FROM     Purchase_Detail   AS tblB_1   WHERE      (Product.Item_Name = Item)) -<br />
<br />
                          (SELECT     SUM(Itm_Qty) AS Quantity FROM      Detail_Description AS tblC_1  WHERE      (Product.Item_Name = Item)) -<br />
<br />
                          (SELECT     SUM(Itm_Qty) AS Quantity FROM      Refund_Detail  AS tblD_1 WHERE      (Product.Item_Name = Item_Name)) AS Total<br />
<br />
FROM   Product GROUP BY Item_Name<br />
<br />
 <br />

create table #myTable (a INT, b INT, c VARCHAR(10))

INSERT INTO #myTable VALUES(1,1,'x')
INSERT INTO #myTable VALUES(1,1,'y')
INSERT INTO #myTable VALUES(1,1,'z')

INSERT INTO #myTable VALUES(2,2,'j')
INSERT INTO #myTable VALUES(2,2,'k')
INSERT INTO #myTable VALUES(2,2,'l')


declare @list VARCHAR(MAX), @a INT, @b INT
SELECT @a=1,@b=1

select @list = coalesce(@list + ',' + c ,c) from #myTable where a=@a AND b=@b
select a,b,@list as c
FROM #myTable
WHERE a=@a AND b=@b
group by a,b

drop table #myTable

i.j.russell wrote:

If you are using Sql Server 2005 or 2008 you can use FOR XML PATH to concatenate data

select cast(id as varchar(20)) + space(2) + cast(id as varchar(20)) + space(2) +
(select string + ','
	from @fk_table fkt
	where fkt.id = pkt.id
	FOR xml path('')) as OutputCol
	from @pk_table pkt
	group by pkt.id

DECLARE @TestTable TABLE(
	[col1] bit NOT NULL PRIMARY KEY,
	[col2] varchar(10) NULL
) 

INSERT @TestTable
  SELECT 0, 'item 1'
  UNION ALL
  SELECT 1, 'item 2'

SELECT *
  FROM @TestTable;

INSERT @TestTable (col1, col2) VALUES (9, 'item 3')

PRINT CAST(9 AS BIT)

INSERT @TestTable (col1, col2) VALUES (1, 'item 42')

I are Troll

Mycroft Holmes wrote:

Slow day up North is it, besides 42 ALWAYS works, your definition of BIT must be wrong.

I are Troll

Eddy Vluggen wrote:

I'm slow in thinking today