Concat All Col2 Values For Each Col1, And Add Sum(col3) (was Query Help)

Sep 11, 2006

Hi,
Can anybody help me to create a single query? I have this problem.


CREATE TABLE t1 (
col1 VARCHAR(100)
, col2 VARCHAR(100)
, col3 INT)

INSERT INTO t1 VALUES('A001','Tom',30)
INSERT INTO t1 VALUES('A001','Rick',40)
INSERT INTO t1 VALUES('A001','Harry',10)

INSERT INTO t1 VALUES('A002','Peter',50)
INSERT INTO t1 VALUES('A002','Sam',50)

INSERT INTO t1 VALUES('A003','Fred',50)


I want a resultset like this ...
i.e col1 col2(all the values would be represented in a single row for each col1) and sum(col3)

(Note: There can be maximum three records for each col1 record,i.e for A001 there can be maximum three records)


A001 Tom Rick Harry 80 --sum(col3)
A002 Peter Sam NULL 100
A003 Fred NULL NULL 50


Any help would be greatly appreciated !!

View 14 Replies


ADVERTISEMENT

Table1(col1) Clustred , Table1(col1,col2,col3) Nonclustered Index , Which One To Keep ?

Jun 27, 2007

For a table 'table1' in sqlserver 2000,

table1(col1) is clustred index 'Ix1' and table1(col1,col2,col3) is nonclustered index 'ix2'



is not tabel1(col1) 'ix1' a duplicate/redundant index ?



which index should be retained ?



or should both indexes remain on the OLTP table ?





Thanks a lot in advance.



View 1 Replies View Related

Trying To Split My Columns Into Years (col1 Must Have Vals For 2005, Col2 Vals For 2006)

Jan 26, 2007

Hi, i'm reasonably new to reporting services and am looking for a way to split my reports' Years to compare the months in year 2005 to 2006 but i can't get my data nest to one another in a single line, it splits the years into different rows

as an example this is what i want if you can decipher that
















2005
2006
Growth
2005 Year to Date
2006 Year to Date
Year to Date Growth




turnover
gross profit
turnover
gross profit
turnover
gross profit
turnover
gross profit










Jan
250500
75300
280200
84100
11.85629
11.686587
250500
75300










Feb
205000
67950
190350
59900
-7.14634
-11.84695
455500
143250
take the month above and add the
current months values


Mar
217670
70540
234200
78000
7.594064
10.57556
673170
213790










Apr
270780
84000
290400
93000
7.245735
10.714286
943950
297790










May
265000
79260
289050
90200
9.075472
13.802675
1208950
377050










Jun
277300
81050
277900
82000
0.216372
1.172116
1486250
458100










Jul


























Aug


























Sep


























Oct


























Nov


























Dec



























Here is my Query:

SELECT /*DT.[YEAR],*DT.[MONTH],*DT.MONTH_NAME,*/ DC.CLIENT_KEY, (select SUM(FT.Cost)where dt.[year] = 2005) AS COST , (select SUM(FT.Price)where dt.[year] = 2005)AS SALES,(select SUM(FT.Cost) where dt.[year] = 2006),(select SUM(FT.Price) where dt.[year] = 2006)--, SUM(FT.QTY) AS QUANTITY, SUM(FT.PRICE) - SUM(FT.COST) AS GP,(SUM(FT.PRICE) - SUM(FT.COST)) / SUM(FT.PRICE) * 100 AS GP_PERCENTAGEFROM FACT_TRANSACTION FT, DIM_TIME DT, DIM_CLIENT DC, DIM_INVOICE_TYPE DIT, DIM_PRODUCT DPWHERE FT.TIME_KEY = DT.TIME_KEYAND FT.PRODUCT_KEY = DP.PRODUCT_KEYAND FT.CLIENT_KEY = DC.CLIENT_KEYAND FT.TYPE_KEY = DIT.TYPE_KEY AND DIT.TYPE_KEY NOT IN (5,6,13,14,15,16,17)AND DC.CLIENT_SERIALNO = '86634'--AND DT.[YEAR] IN(2005,2006)AND DT.[MONTH] IN(1,2,3,4,5,6,7,8,9,10,11,12)AND DP.PRODUCT_KEY <> 1668684GROUP BY DT.[YEAR],DC.CLIENT_KEY--, DT.[MONTH]ORDER BY /*DT.[YEAR],*/DT.[MONTH]
but it returns everything under one another

2005 1 January 2005 3 296092.3431 405263.62 12811 109171.2769 26.93
2005 2 February 2005 3 318597.658 432098.17 13220 113500.512 26.26
2005 3 March 2005 3 371327.721 506481.46 15283 135153.739 26.68
2005 4 April 2005 3 371647.994 504713.99 15491 133065.996 26.36
2005 5 May 2005 3 400870.6138 542759.57 16296 141888.9562 26.14
2005 6 June 2005 3 399673.0086 546110.59 16607 146437.5814 26.81
2005 7 July 2005 3 390477.7521 535531.40 16153 145053.6479 27.08
2005 8 August 2005 3 380628.57 520281.87 15800 139653.30 26.84
2005 9 September 2005 3 340949.8849 471861.17 14820 130911.2851 27.74
2005 10 October 2005 3 340240.804 470007.78 14444 129766.976 27.60
2005 11 November 2005 3 349156.1871 481193.61 14523 132037.4229 27.43
2005 12 December 2005 3 346038.5059 477011.72 14865 130973.2141 27.45
2006 1 January 2006 3 340062.1369 470010.08 14037 129947.9431 27.64
2006 2 February 2006 3 328463.9689 452404.79 13996 123940.8211 27.39
2006 3 March 2006 3 375264.977 517800.27 16065 142535.293 27.52
2006 4 April 2006 3 412708.965 567014.52 17550 154305.555 27.21
2006 5 May 2006 3 446973.4231 606476.26 18920 159502.8369 26.29
2006 6 June 2006 3 406072.4943 544634.77 17053 138562.2757 25.44
2006 7 July 2006 3 389104.6316 526091.14 16228 136986.5084 26.03
2006 8 August 2006 3 317810.4531 431530.58 13641 113720.1269 26.35
2006 10 October 2006 3 405230.7083 549310.72 17151 144080.0117 26.22
2006 11 November 2006 3 379788.6645 514554.14 15917 134765.4755 26.19
2006 12 December 2006 3 393235.0906 531582.69 16924 138347.5994 26.02

If i do get them split then it put every year's value on a different line

2005   1234123.34    32432432.43   NULL   NULL
2006   NULL               NULL           12312.212   15235453.21

Please Help,

View 1 Replies View Related

Query, Distinct And Text Concat

Jan 30, 2008

Hi,

I have the following table called "tests" :
id WeekNbrnametest hours
--------------------------------------------------------------------------
1 2007/26John "testA"5
2 2007/26John "testB"6
3 2007/26David "testA"3
4 2007/28David "testC"2
5 2007/30Victor "testD"1

I want to write a query so that I have as a result one row per person and per week, as followed

WeekNbrname testhours
--------------------------------------------------------------------------------
2007/26John"testA, testB" 11
2007/26David"testA" 3
2007/28David"testC" 2
2007/30Victor"testD" 1

This means that I need to concatenate the values of the test column if in the same week and same person.
For now, I have only managed to do the job without the test concatenation the following way:

SELECT DISTINCT WeekNbr, name, SUM(hours) as [Total of hours]
FROM tests
GROUP BY WeekNbr, name

and I get the following:
WeekNbrnamehours
-------------------------------------------------------
2007/26John 11
2007/26David 3
2007/28David 2
2007/30Victor 1


Anyone could help me please?

Thanks so much in advance

Pierrot

View 1 Replies View Related

Data For Source Column 3('Col3') Is Too Large For The Specified Buffer Size.

Aug 24, 2007


Hi,

I have a problem to import xls file to sql table, using MS SQL 2000 server.
Actual main problem associated with it is xls file contain one colum having large amount of text which length is approximate 1500 characters.
I am trying to resolve it through like save xls to csv or text file then import but it also can not copy whole text of that column, like any column in xls having 995 characters then text or csv file contain 560 characater. So, it is also wrong.

thanks in advance, if any try to resolve

View 1 Replies View Related

How To Select Distinct Row By Col1 ?

Jan 23, 2008

HI

Please help I didn't get right solution for this problem. So I am posting again with full details.
Scenario

col1---col2------col3----col4-----col5------col5
123-----AB--------WE-----Name------Add------Prod1
123-----AB--------DC-----Name------Add------Pro512
123-----AB--------FR-----Name------Add------Prt78
389-----AB--------DC-----Name------Add------Prt78
482-----AB--------DC-----Name------Add------Prt78

How do I select Distinct row by col1 from the above scenario. Expected result will be

123-----AB--------WE-----Name------Add------Prod1
389-----AB--------DC-----Name------Add------Prt78
482-----AB--------DC-----Name------Add------Prt78

OR

123-----AB--------DC-----Name------Add------Pro512
389-----AB--------DC-----Name------Add------Prt78
482-----AB--------DC-----Name------Add------Prt78

OR

123-----AB--------FR-----Name------Add------Prt78
389-----AB--------DC-----Name------Add------Prt78
482-----AB--------DC-----Name------Add------Prt78


Please Help.

View 12 Replies View Related

[Microsoft][ODBC Text Driver] In The File Specification '...', The Col1 Option Is Invalid.

Mar 23, 2007

I'm trying to connect to a tab-delimited text file using the ODBC data access. I set it up using Administrative Tools - Data Sources (ODBC) from the start menu, and clicked the 'Guess' button under Define Format.

The schema it created looks like this:

[cims_output_data.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=0
CharacterSet=OEM
Col1=SIMNAME
Col2=TIMESTAMP
Col3=SIMTYPE
Col4=REGION
Col5=SECTOR
....etc.

It looks OK, but if I go back to Define in Administrative Tools and try to review it, I get a similar error message:

Ini File (or Registry) C:Program FilesCIMSschema.ini is corrupt.
Section: cims_output_data.txt, Key: Col1.

Has anyone seen this before? There is nothing on MSDN as far as I could find. I read on another forum that if you replace the label 'Col1' with 'Col' the problem is solved but it still reports that the schema is corrupt when I try to open it in Administrative Tools.

Bill.

View 5 Replies View Related

Concat

Apr 19, 2007

Hi peso, hi KH, Hi everyone....

Declare @day int
set @day = 3

select right('0' + @day, 2)

My expected result is : 03
If single integer - will append in the right a '0'.
If 2 digit integer - will return the actual value.

Thanks.

-Ron-

"If you can only access one site on the Internet, make it SQLTeam!"

View 4 Replies View Related

Concat?

Jan 29, 2008

I am trying to return rows from a query and I want to display the information from 2 columns into one. Here is my query:

SELECT TOP (100) PERCENT dbo.CASES.CaseID, dbo.CASENUMBERTYPES.CaseNumTypeDesc, dbo.CASENUMBERS.CaseNumber,
dbo.SCANNEDDOCUMENTS.TopicID, dbo.EVENTS.EventTypeID, dbo.EVENTS.ev_StartDate, dbo.SCANNEDDOCUMENTS.Doc
FROM dbo.SCANNEDDOCUMENTS INNER JOIN
dbo.CASES ON dbo.SCANNEDDOCUMENTS.CaseID = dbo.CASES.CaseID INNER JOIN
dbo.CASENUMBERS ON dbo.CASES.CaseID = dbo.CASENUMBERS.CaseID INNER JOIN
dbo.CASENUMBERTYPES ON dbo.CASENUMBERS.CaseNumTypeID = dbo.CASENUMBERTYPES.CaseNumTypeID INNER JOIN
dbo.EVENTS ON dbo.CASES.CaseID = dbo.EVENTS.CaseID
WHERE (dbo.CASENUMBERTYPES.CaseNumTypeDesc IN ('cc', 'dc')) AND (dbo.EVENTS.EventTypeID IN (7048, 6467)) AND
(dbo.SCANNEDDOCUMENTS.TopicID IN (2, 3, 4)) AND (dbo.EVENTS.ev_StartDate > CONVERT(DATETIME, '2008-01-13 00:00:00', 102)) AND
(dbo.EVENTS.ev_StartDate < CONVERT(DATETIME, '2008-01-19 00:00:00', 102))
ORDER BY dbo.CASENUMBERTYPES.CaseNumTypeDesc, dbo.CASENUMBERS.CaseNumber

I get this for my return






1526959
CC
228762
2
7048
1/16/2008 12:00:00 AM
<Binary data>
CC is my CASENUMBERTYPES and the next column (228762) is CASENUMBERS

How can I get it to return it to where the CASENUMBERTYPES and CASENUMBERS show together in one column?

View 7 Replies View Related

Concat Rows Help

Jun 11, 2008

Hi there, I'm trying to concat the results of a select statement into a single row separated by commas without duplicates. The query and output is something like this:SELECT Specification FROM Cars WHERE Model = 112Which would return something like:Specification------------------Model 1Model 2Model 2Model 4 What I'd like is these to be combined into a single row such as:Specification
------------------Model 1, Model 2, Model 4 

View 12 Replies View Related

Ok To Concat A Uniqueidentifier?

Jul 8, 2004

I want to use a NEWID() to generate order numbers, but i dont want to give customers the long uniqueID. so im wondering if i concatinate it to 8 characters, if that would be safe or not...

thx in adv

View 6 Replies View Related

Concat Int With String

Feb 11, 2005

Hi,

I am trying to concat an int with an nvarchar in my select:

select distinct(id + name)
from load
where name not like '%test%'

but I am getting an error saying:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value '灪愭楮敭' to a column of data type int.

My question is: how do you concat int and string in MS SQL Server. The equivalent in Oracle would be ||, I think.

Thanks for any help.

Mongo

View 1 Replies View Related

String Concat

Feb 24, 2006

Hello,

I have a string let's say: "123".
I want to convert it to:
-- Start SqlServer
BEGIN
{CALL DBA.applsp_ChangeComments( 'ArtPurchPriceUpdate', 'TRIGGER', 'nl', 'ArtPurchPriceUpdate', '1' + CHAR(13)+ CHAR(10) +
'2' + CHAR(13) + CHAR(10) +
3' )}
END;
-- End SqlServer

-- Start Sybase
BEGIN
CALL DBA.applsp_ChangeComments( 'ArtPurchPriceUpdate', 'TRIGGER', 'nl', 'ArtPurchPriceUpdate', '1' + CHAR(13) + CHAR(10) +
'2' + CHAR(13) + CHAR(10) +
'3' );
END;
-- End Sybase

-- Start Oracle
BEGIN
ish.applsp_ChangeComments( 'ArtPurchPriceUpdate', 'TRIGGER', 'nl', 'ArtPurchPriceUpdate', '1' || CHR(13) || CHR(10) ||
'2' || CHR(13) || CHR(10) ||
'3' );
END;
/
-- End Oracle

This works well for oracle and sybase, for sqlserver I can't concatenate in a parameter of a stored procedure. I really want to do this. To create a variable is a lot of extra programming. Is there anyone who knows how to cancat these strings within the stored procedure call?

thanks beforehand,

Coen Dunnink
The Netherlands

View 7 Replies View Related

CONCAT + Expression

Mar 2, 2006

how can i CONCAT 2 columns & () ?

SELECT CONCAT(A,B) AS C From myTAble

but I want to get A (B)
dog (red)


thank you

View 2 Replies View Related

How To Concat More Than 1 Columns?

Dec 8, 2006

Hi,

i want to select 2 integer and a varchar fields as one. How can i do it?
Any ideas?
thanks

View 8 Replies View Related

Concat Columns

Jun 7, 2007

Hi everyone I'm trying to get 4 columns from my table to be on column, not sure how to go about it.

The column names are
X_POLICY_NO,
X_POLICY_EFCTV_DT,
PRODUCT_ABBR,
X_ASCO_CD

I would like to take those 4 columns in the select statement and make them on, I did in access but I tried

Select
(X_POLICY_NO, X_POLICY_EFCTV_DT, PRODUCT_ABBR, X_ASCO_CD) as CONCAT but i get an error that says :

Incorrect syntax, thanks for the help its greatly appreciated

View 5 Replies View Related

Concat Error

Jul 2, 2007

I'm trying to get month/year from column X_POLICY_EFCTV_DT, i tried this

Concat(month(rr.X_POLICY_EFCTV_DT),
year(rr.X_POLICY_EFCTV_DT))

But i'm getting an error concat is not a recognized function name

is there a better way to get month/year from that column?

View 2 Replies View Related

Concat Stirng

May 13, 2008

i have dataset like following:

ID name
============================
1 John
2 Dave


what i wan to do is show "John,Dave" in a single textbox, what should i do ? do i need a matrix table?



thx,

View 2 Replies View Related

Concat Rows Help Without Duplicate

Jun 11, 2008

Hi there, I'm trying to concat the results of a select statement into a single row separated by commas without duplicates. The query and output is something like this:

SELECT Specification FROM Cars WHERE Model = 112

Which would return something like:

Specification
------------------

Model 1
Model 2
Model 2
Model 4


What I'd like is these to be combined into a single row such as:

Specification
------------------

Model 1, Model 2, Model 4

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

View 1 Replies View Related

Concat Symbol % With Result?

Aug 30, 2013

SELECT ROW_NUMBER() OVER (ORDER BY BM.BILL_NUMBER_V) AS [SL_NO],PP.KID_ID_NO_V AS [KID_NO],(PP.FIRSTNAME_V + SPACE(1) + PP.LASTNAME_V)AS [PATIENT_NAME],
CONVERT(VARCHAR(10),PP.UPDATEDDATE_D,101)AS [VISIT_DATE],BM.BILL_NUMBER_V AS [BILL_NUMBER],CONVERT(VARCHAR(10),BM.BILL_DATE_D,101) AS [BILL_DATE],
ROUND(BM.BILL_AMOUNT_M,2) AS [BILL_AMOUNT],ROUND(BM.CONCESSION_AMOUNT_M,2) AS [CONCESSION_AMOUNT],ROUND(BM.TOTAL_AMOUNT_M,2) AS [TOTAL_AMOUNT],

[Code] .....

In the above query i want to concat symbol '%' for output of percentage column. How to do that?

Eg: PERCENTAGE
30.00%
50.00%

View 1 Replies View Related

Concat To Create A Date

Nov 6, 2007

will this work to create a whole date if YEAR_OPENED is a solid year. Like 1957 for example?

org_date_founded = YEAR_OPENED+'01-01'

View 3 Replies View Related

Concat Tables Into One Row In View

Jun 26, 2007

If I have table1 and table2 with table2 having multiple rows tied to asingle row in table 1.What I am trying to do is set up a view that has one row that showsthe followingtable1.uniqueid, table1.name, table2.row1.detail, table2.row2.detail,table2.row3.detailI'd like to be able to do a select on the view and only come back withone row per widget. If possible, I'd actually like to be able toconcat 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.dateCreatedthxM@

View 1 Replies View Related

Concat Instead Of SUM When Grouping Results

Nov 23, 2007

Hello,I have a very simple problem which I will illustrate with an example:I have the following records in my table:A 1 CA 2 CA 3 CB 8 KB 9 KI now want to group them and the result has to be:A 1,2,3 CB 8,9 KSo the results in the second row have to be concatenated. I guessthere is no function to do this... What is the simplest solution?Kind regards,Bart Warnez

View 11 Replies View Related

How To Concat Varchar In This Case?

Nov 25, 2007

Hi,
I try to create a string that represant a insert statement but I've some problems to concat :

Set @Insert = 'INSERT INTO DEMANDES VALUES (' + @AvionID + ',' + @DemandeID + ',' + @Consommation + ',' + @Vitesse + ',' + @TailleReservoire + ',''' + @PlageHoraireDebut +''','''+ @PLageHoraireFin+''','''+ @VilleDepart+''',''' +@VilleDestination+''');';

AvionID is a int
DemandeID too
Consommation too
Vitesse too,
TailleReservoire too,
PlageHoraire varchar and ville too.

Can you explain me how to concat these strings?

Thanks for your help!

View 5 Replies View Related

Concat Null Strings In View

Jan 17, 2005

I want to concat strings such that if one string is null, it is treated as an empty string. For example 'abc' + NULL = 'abc'.

SQL Server has the CONCAT_NULL_YIELDS_NULL defaulted to ON, and it needs to be on in most cases when dealing with indices. (INSERT, DELETE, UPDATE, etc.) However, when I am running a select, and one field has a null value, I still want to see the other fields in the resulting string.

I can do this in a function, but I need a view, and the view doesn't seem to let me save a command with 'SET' in it. (However, it does let me run the command! And it works!) It's just when I try to save it, I get a syntax error, so the view is unavailable to me as I want it.

The command that works, but won't save is:

PHP Code:




 SET CONCAT_NULL_YIELDS_NULL OFF
SELECT t1.field1+'-'+'t2.field2 AS viewField
  FROM table1 t1 INNER JOIN
           table2 t2 ON t1.fk1=t2.pk2 





The tables look like this:

Table1

PHP Code:




 create table user.dbo.table1
(
  pk1 int primary key,
  fk1 int,
  field1 varchar(32),
  FOREIGN KEY fk1 REFERENCES table2 (pk2)






Table2

PHP Code:




 create table user.dbo.table2
(
  pk2 int primary key,
  field2 varchar(32)






table1

PHP Code:




 pk1 fk1 field1
 1     1    'test1'
 2     2    'test2'
 3     1    'test3' 





table2

PHP Code:




 pk2  field2
 1     NULL
 2     'tab2 test' 






I expect to see:

PHP Code:




 ViewField
test1-
test2-tab2 test
test3- 

View 2 Replies View Related

CONCAT Function + SQL Sever 2005

Nov 29, 2006

I cant find a clear answer to the syntax of concat in for SQL 2005. Can anyone help.

View 3 Replies View Related

How To Concat/pivot Rows To Column?

Apr 6, 2004

hello,

I'm wondering how it's possible to have a select statement resultant rows concatenated into one row and column.
For example:
select letter from alphabet_table
a
b
c
d
e
...
26 rows returned.

Other than a cursor, how would I write a query to return the following:
row1: abcdefghijkl...

thanks in advance!

View 5 Replies View Related

T-SQL (SS2K8) :: Error In String By CONCAT

Jan 22, 2015

i have 2 table:

t1:
t1_idint
namenvarchar(10)

t2:
t2_idint
t1_idint
Sematnvarchar(50)

then i write following SQL Command

SELECT dbo.T1.t1_id, dbo.CONCAT(dbo.T2.product) AS product
FROM dbo.T1 INNER JOIN
dbo.T2 ON dbo.T1.t1_id = dbo.T2.t1_id
GROUP BY dbo.T1.t1_id

but following error is be shown. why? Msg 4121, Level 16, State 1, Line 1 Cannot find either column "dbo" or the user-defined function or aggregate "dbo.CONCAT", or the name is ambiguous.

View 3 Replies View Related

Concat With Auto-increment Column

Aug 29, 2007

I have a column with consist of customer number.e.g.
c001
c002
c003

How do I add the character "c" to the auto-incremental number everything I add?

View 7 Replies View Related

SQL Server 2012 :: Concat Parameter In A Message

Jun 2, 2014

I have a procedure (a) where i call another procedure (b) passing @message as a parameter to procedure B.

Ex:
Create procedure a
as
Begin
declare @prevyear
exec b @message = 'avvasdva' + cast(@prevyear as varchar)
End

When i execute above procedure, i get error at + sign i.e. @message parameter. how can i concatenate string with another parameter when passing parameters

View 3 Replies View Related

SQL 2012 :: Group Concat To Make 2 Rows Into 1?

Oct 14, 2014

I have 2 different types of data for the same key. My queries renders these results into 2 rows. I'd like them to be one row.

Example

DOG Name_a Color_a Age_a Name_B Color_B Age_B
Boxer BO Fawn 1
Boxer Maggie Brindle 4
Lab Jackson Yellow 2
Lab Sandie Black 3

The result I want

DOG Name_a Color_a Age_a Name_B Color_B Age_B
Boxer BO Fawn 1 Maggie Brindle 4
Lab Jackson Yellow 2 Sandie Black 3

View 1 Replies View Related

Fast Way To Concat Records To Comma Sep VARCHAR

Jul 20, 2005

Hi All,Here's a challenge.If there is a 'one word' answer to this, i.e. the name of a built inSQL Server function I can use, that would be great! However...I have a standard 1:m relationship, e.g. tblHeader and tblDetail.I want to create a view of records from 'tblHeader' and within thatview have a column (called e.g. DetailTypes) that provides a singlecomma separated varchar of values from a varchar field (called e.g.DetailType) that appears in related records in the 'tblDetail' table.(hope you understood that :)e.g. the contents of my 'tblDetail' table could be....Detail ID | Header ID | DetailType-----------------------------------1 | 1 | A2 | 1 | A3 | 1 | B4 | 2 | A5 | 2 | C6 | 3 | BTherefore, the view I want to create should return:Header ID | DetailTypes-------------------------------1 | 2A, 1B2 | 1A, 1C3 | 1Bi.e. the first row can be read as "Header 1 has 2 'A' detail recordsand 1 'B' detail record."I have created a view e.g. 'vqryHeaders' which calls a user definedfunction that takes the HeaderID and opens a cursor on another view,e.g. 'vqryDetailGrouped'. The other view groups the records intblDetail so that I can get a count of each DetailType for each HeaderID. The cursor then loops through the returned records concatenatingthe count and detail type into a comma separated string (as shownabove).However, when I run this across 20k records it is soooo sloooooww. Ihave indexes on the relationship fields and I am using realisticallysized varchars - neither made any difference in speed. It isdefinately the function that I wrote that slows it down as the view islightning fast when I remove my function call.I can supply source code if necessary, but I think that this is akind-of generic problem so I don't see the point - yet.I really hope you can help.Regards,Jezz

View 11 Replies View Related

Concat With A Loop - Convert Number Into String Character

Apr 5, 2014

I'm new to SQL and I've been trying this for a while now.

I have let's say a loop of numbers from one to ten. It appears like this:

1
2
3
4
5

What I want to do is to have it appear on one column like this: 12345, the problem is I can't seem to figure out if I need to only have one variable or I'm missing something else, I figured you need to convert them into a string character but I'm still unable to do it.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved