Converting Rows To Columns Using Case.Problem With

Mar 28, 2008

SELECT
h1.ftraccode,
CASE WHEN FTRAADDRED='A' then h1.ftrascode end as 'From Sec',
CASE WHEN FTRAADDRED='r' then h1.ftrascode end as 'To Sec',
case when ftraaddred ='A' then h1.ftradesc end as 'From Description',
case when ftraaddred ='r' then h1.ftradesc end as 'to Description'
from bHISfile h1
where h1.ftradesc like 'sw%'
order by 1
----------------------------------------------------------------
clintcode |from_sec | to_sec| from_desc | to_desc
---------------------------------------------------------------
ABADJ16421 |NULL | MMTEI |NULL |SWITCH TO OAPIF
ABADJ16421 |OAPIF | NULL |SWITCH FROM MMTEI |NULL

2(row)

Expected output like this

----------------------------------------------------------------
clintcode |from_sec | to_sec| from_desc | to_desc
---------------------------------------------------------------
ABADJ16421 |OAPIF | MMTEI |SWITCH FROM MMTEI |SWITCH TO OAPIF

1(row)

View 3 Replies


ADVERTISEMENT

Arranging Data On Multiple Rows Into A Sigle Row (converting Rows Into Columns)

Dec 25, 2005

Hello,
I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:
user1   answer1user1   answer2user1   answer3user2   answer1user2   answer2user2   answer3
For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer):
user1   answer1   answer2   answer3user2   answer1   answer2   answer3
How can this be done? How can all answers of a user appear on a single row
Thanx,Danny.

View 1 Replies View Related

Converting Rows Into Columns

Sep 20, 2004

Hi

How can one convert rows into columns (or all rows in one column as a single row, except each row in its own column), either by using a temperary table or just in a select statement?

View 2 Replies View Related

Converting Rows Into Columns

Jun 22, 2008

hi,

i have the 4rows in one table those are book names...

book1
book2
book3
book4


i have the other table..consisting of usenames

in the output i need like this

username1 book1 book2 book3 book4
username2 book1 book2 book3 book4

View 1 Replies View Related

Converting Rows To Columns

Jul 20, 2005

have a urgent requirement. Please somebody help me.I have a table departinfo with following recordsbegin_time end_time Name Pieces10:00 10:15 PopCorn 310:15 10:30 Biscuits 510:30 10:45 PopCorn 2Now I need to run a sql query and the output should be as below :begin_time end_time PopCorn Biscuits10:00 10:15 3 010:15 10:30 0 510:30 10:45 2 0Please note that only one column i.e. PopCorn is created in spite ofhaving multiple records in the table. Similarly the records are notfixed. I mean thatthere can be n number of records and the columns should be uniquelycreated.Can somebody help me outPLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ

View 7 Replies View Related

Converting Rows Into Columns MS SQL 2K

Jul 20, 2005

I have a SP that returns the information I want but it returns it in 2separate queries.Example:Query 1Name, Number, ClassRow 1- Mike Phillips, 154AA, AAandQuery 2Time, ManualRow 1 -12:45:22,0Row 2 -13:04:56,0What I want it to look like is:Name, Number, Class, Time 1, Manual 1, Time 2, Manual 2Row 1- Mike Phillips, 154AA, AA, 12:45:22, 0, 13:04:56, 0Here is the query I'm using:DECLARE Class cursorFOR--here we get a list of distinct classes to pass to the Class cursorselect Distinct(class_ID) from kt_member_lapwhere Race_ID = 83order by Class_ID;OPEN Class;DECLARE @RaceID intDECLARE@RacerCount intDECLARE @ClassID char(50)DECLARE @classcountDECLARE @Racer char(50)DECLARE @i intSET @RaceID = 83--this is where we loop through the classesFETCH NEXT FROM Class INTO @ClassIDWHILE (@@FETCH_STATUS <> -1)BEGINIF (@@FETCH_STATUS <> -2)DECLARE Lap cursorFORSelect DISTINCT(Member_ID) from KT_MEMBER_LAPWhere class_ID = @classID and race_id = @RaceIDOPEN Lap;--this is to begin counting from the first lapSET @i = 1;FETCH NEXT FROM Lap INTO @RacerWHILE (@@FETCH_STATUS <> -1)BEGINIF (@@FETCH_STATUS <> -2)SELECT KT_MEMBER.MEMBER_FNAME + ' ' +KT_MEMBER.MEMBER_LNAME As MemberName,CONVERT(nvarchar(3),KT_MEMBER_CLASS.MEMBER_CLASS_BIKE_NUM) + KT_CLASS.CLASS_LETTER AsBikeNumber,KT_CLASS.CLASS_DESCFROM KT_CLASS INNER JOINKT_MEMBER_CLASS ON KT_CLASS.CLASS_ID =KT_MEMBER_CLASS.CLASS_ID INNER JOINKT_MEMBER ON KT_MEMBER_CLASS.MEMBER_ID =KT_MEMBER.MEMBER_IDWHERE KT_MEMBER.MEMBER_ID = @Racer and KT_CLASS.CLASS_ID =@ClassID--SELECT @Racer, @ClassIDSelect MEMBER_LAP_TIME_REAL, member_lap_manual from KT_MEMBER_LAPWhere Member_ID = @Racer and class_ID = @classID and race_id =@RaceIDORDER BY MEMBER_LAP_TIME_REAL--here I count up for the next lapSET @i = @i + 1;FETCH NEXT FROM Lap INTO @RacerENDCLOSE Lap;DEALLOCATE Lap;FETCH NEXT FROM Class INTO @ClassIDENDCLOSE Class;DEALLOCATE Class;Any help would be appreciated.

View 1 Replies View Related

Converting Rows Into Columns

Mar 10, 2008



Hi,
I have written a query that I need to get data which are to be displayed in the SQL 2005 reports.
I am getting the result as

Data Count
A 2
B 4
C 5


I need to get the data in a single row and the data in the 'Data' column should become like a column, like

A B C
2 4 5

Can anyone help me with this query?

View 5 Replies View Related

Converting Rows To Columns In SQL7

Jul 20, 2005

I do a:SELECT * FROM xxxAnd Get:Date Place SumA M 1A O 3 A P 2B O 5B M 4B P 2And I want it to look like:Date M O PA 1 3 2B 4 5 2Can you think of an EASY way to do this?I can do it with a cursor that constructs a SQL statement, which I EXEC, but the 8000 character limit may prove to be a limiting factor.sp_execsql is somewhat messy for the nature of this issue.Any input is appreciated.Thanks in advance.

View 2 Replies View Related

Transact SQL :: Turn Rows Into Named Columns Using CASE

Nov 19, 2015

That may not be the most exact TITLE I have used for a question as CASE may not be the best expression to use but I have a query:

select AR.ClientID,
AR.ReferenceID,
AAC.SequenceID,
AA.AppearanceDate,
CASE
WHEN AAC.Outcome IS NULL Then 'Cancelled' else

[Code]....

This returns the following data set:

ClientID ReferenceID
SequenceID AppeareanceDate
Outcome Rownum

1000000 2
2000358 2005-11-07
Yes 1

[Code] ...

What I want the eventually solution to be is that for each unique/distinct client, reference and sequence there is one record (row). Based on the row_number, I want the first 'AppearanceDate' renamed to 'AppearanceDate1' with the first AppearanceDate as the data and the first Outcome to be renamed 'Outcome1' with the first Outcome as the data. Any subsequent rows would follow as columns:

ClientID ReferenceID
SequenceID AppeareanceDate1
Outcome1  AppearanceDate2
Outcome2
1000000 2
2000358 2005-11-07 
Yes NULL

[Code] ...

Only four AppearanceDates and outcomes are required based on the unique/distinct Client, Reference and SequenceIDs. How I can loop through the Appearances based on the row_number and show each record in one row?

View 3 Replies View Related

Crazy SQL Statement Help Needed... Converting The Rows To Columns (sort Of)...

Sep 19, 2006

This one isn't so simple.I have a list of training modules, training complete dates and a list of employees in separate tables. I'll give an good example in a second. The problem I am having is that I need to generate a select statement that will generate a kind of 'spreadsheet' that will list the employees in the rows, and columns containing the results in the fields (the training module may or may not have been completed, and thus may or may not be in the result box. I think the example explains it fairly well (note, I did not design the database structure but have to work with it).Employees table:empNameJane DoeAlton BrownJohn DoeTrainingCourse table:courseNameWeldingBrain SurgeryScuba DivingResults table:empName:    courseName:      completeDate:Jane Doe      Welding              2/2/2002Jane Doe      Brain Surgery      3/7/2005Alton Brown  Scuba Diving       9/23/2004Alton Brown  Welding              11/4/2004John Doe      Brain Surgery      6/14/2003End result of select statement:                        Welding                Brain Surgery             Scuba DivingJane Doe         2/2/2002                   3/7/2005                     Alton Brown    11/4/2004                                                  9/23/2004John Doe                                          6/14/2003 Thanks a million to anyone with insight into this. I'm still trying to figure out a way to do this, but after a few days haven't come up with or found anything. Most things I've found online are too simplistic.

View 8 Replies View Related

PIVOT/CROSS TAB/Converting Rows To (multiple Group) Columns

Aug 3, 2007

Hello All,

I am trying to convert the rows in a table to columns. I have found similar threads on the forum addressing this issue on a high level suggesting the use of cursors, PIVOT Transform, and other means. However, I would appreciate if someone can provide a concrete example in T-Sql for the following subset of my problem.

Consider that we have Product Category, Product and its monthly sales information retrieved as follows:













CategoryID
ProductID
ProductName
Month
UnitPrice
QtySold
SalesAmount

1
1
Panel
Jan
5
10
50

1
1
Panel
Feb
5
15
75

1
1
Panel
Mar
5
20
100

1
2
Frame
Jan
10
30
300

1
2
Frame
Feb
10
25
250

1
2
Frame
Mar
10
20
200

1
3
Glass
Jan
20
10
200

1
3
Glass
Feb
20
20
400

1
3
Glass
Mar
20
30
600

I would like it to be converted into following result set:















CategoryID
ProductID
ProductName
UnitPrice
QtySold_Jan
SalesAmt_Jan
QtySold_Feb
SalesAmt_Feb
QtySold_Mar
SalesAmt_Mar

1
1
Panel
5
10
50
15
75
20
100

1
2
Frame
10
30
300
25
250
20
200

1
3
Glass
20
10
200
20
400
30
600

I have purposefully included QtySold here as I need to display both Quantity and Sales as measured column groups in my report. Can this be achieved in sql? I would appreciate any responses.

Thanks.

View 1 Replies View Related

T-SQL (SS2K8) :: Converting Row Values To Columns With Dynamic Columns

Jun 11, 2015

Basically, I'm given a daily schedule on two separate rows for shift 1 and shift 2 for the same employee, I'm trying to align both shifts in one row as shown below in 'My desired results' section.

Sample Data:

;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS
(
SELECT 5060,'04/30/2015','05:30', '08:30'
UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30'
UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30'
UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'

[Code] ....

The results from the above are as follows:

columnAcolumnB SampleTitle1 SampleTitle2 SampleTitle3 SampleTitle4
506004/30/201505:30 NULL NULL NULL
506004/30/201513:30 15:30 NULL NULL
506005/02/201505:30 NULL NULL NULL
506005/02/201513:30 15:30 NULL NULL

My desired results with desired headers are as follows:

PERSONSTARTDATE STARTIME1 ENDTIME1 STARTTIME2 ENDTIME2
506004/30/2015 05:30 08:30 13:30 15:30
506005/02/2015 05:30 08:30 13:30 15:30

View 3 Replies View Related

Converting To Upper Case

Aug 13, 2001

Is there a method for converting the first character of a account name to uppercase and the the remaining characters to lower case? I've used the substring procedure but for a name like 'MY NEW COMPANY', how could I convert it to 'My New Company' ?
Thanks,
Terry

View 1 Replies View Related

Converting CASE Statement

Dec 20, 2013

I have a case statement which I need to alter in my query. The field [sr.spec_value] contains either 1 or 0. When it is 1 I want the case expression to return 'YES' when 0 then 'NO'. I've tried a couple of options from the forum but am not getting it right. Here is the original case statement from my query:

case when sr.spec_value = 1 then 'YES' when 0 then 'NO' end [Fixed].

View 5 Replies View Related

Converting Date In Case Statement

Apr 29, 2015

I have a query where I need to see in 2 seperate columns, the Actual date of deliveries and collections, and the Invoice Week number of the same. The problem is that in our ERP system delivery date (di.ddate) is a date field, and the collection date is nvarchar. This is my query and sample results below, as you can see I'm getting nulls for the collection lines. How can I do this efficiently

select di.dticket, di.item,
case di.item when 'DEL' then di.ddate when 'COL' then CONVERT(datetime,di.text2,103) end ActDate,
Case when di.item = 'DEL' then
(select YearWeek from MCSReports.RptRevenueGuideWeekLookup
where di.ddate between FromDate and ToDate) end InvWeek

from deltickitem di
where item in ('DEL','COL')

Sample results below:

dticket item ActDate InvWeek
-------------------- -------------------------------------------------- ----------------------- --------
01-000002 DEL 2011-07-05 15:53:00.000 2011-27
01-000002 COL NULL NULL
01-000004 DEL 2011-07-05 16:04:00.000 2011-27
01-000004 COL NULL NULL
01-000005 DEL 2011-07-05 16:08:00.000 2011-27
01-000005 COL NULL NULL
01-000006 DEL 2011-07-05 16:19:00.000 2011-27
01-000006 COL NULL NULL
01-000007 DEL 2011-07-05 16:24:00.000 2011-27
01-000007 COL NULL NULL
01-000008 DEL 2011-07-05 16:29:00.000 2011-27
01-000021 DEL 2011-07-05 08:48:00.000 2011-27

View 6 Replies View Related

Converting Tables To Upper Case

Jul 23, 2005

Hello, we've an Oracle transition in the pipeline and want to convertall our database objects to upper case. Any one got a script ortechnique (other than manual) to do it?Many thanks, Kevin.

View 2 Replies View Related

Converting Data Like A Case Statement

Oct 10, 2006

Hello.

I have data in a SSIS package that I need to alter to something else.

The source column is a VARCHAR(3) column and it only contains two possible values, "ACT" or "CLS".

The destination column is a CHAR(1) column. Where the value of the source column is 'ACT' I want to put '1' in the destination and where the value of the source column is 'CLS' I want to put '0'.

I can do this easily in T-SQL using a CASE statement but the source data is an Ingres database and CASE isn't a valid SQL keyword.

Can I use a data conversion task to do this in SSIS? and if so, what's the syntax?

Thanks









View 10 Replies View Related

Converting A String Expression To Proper Case

Oct 22, 2000

How would I convert an expression like on of these to all upper case first letters with remaining letters lower case? VB has a function for that but sql doesn't seem to. I thought about having a loop go through each character to check for spaces. I've written a couple of similar pieces of code in VB when a while ago, but is there a better way? Thanks :)

Just a couple of typical examples of how the data should appear ~

payment, credit card ==> Payment, Credit Card
butcher & singer ==> Butcher & Singer

View 1 Replies View Related

URGENT HELP CONVERTING SYBASE To SQL Case -sensi..

Aug 25, 2004

IN Sybase

SP_HELPSORT is Producing the following

------------------------------

Sort Order Description

------------------------------------------------------------------
Character Set = 2, cp850
Code Page 850 (Multilingual) character set.
Sort Order = 50, bin_cp850
Binary ordering, for use with Code Page 850 (cp850).
Characters, in Order


What would be equivalent in SQL Server 2000 .

PLease let me know the Colation Name ASAP.

Greatly appreciate your help

Thanks

View 1 Replies View Related

Need Help Converting A Select Query Into A Case Statement

Nov 24, 2004

I have the following query:

(SELECT MIN(CFGDates.AccountPdEnd)
FROM CFGDates LEFT JOIN
AR ON AR.Period = CFGDates.Period
WHERE AR.Period = '200408')


I need to convert this into a case statement.
I tried various ways but did not get the result that I was after

Thanks,
Laura

View 6 Replies View Related

Converting Case Statement Into Config Table

Nov 3, 2015

I have a stored procedure in which we are deriving some flags. So, we used series of CASE statements.

For examples

CASE
     WHEN LEFT(CommissionerCode, 3) IN ('ABC','DEF',...) THEN 1
     WHEN PracticeCode IN (.......) THEN 1
     WHEN (CommissionerCode IN (.....) OR PracticeCode NOT IN (.....) OR .....) THEN 1
      ELSE 0
END

I need to put these conditions in config table and generate dynamic sql. What is the best way to do this? especially, 3rd condition with OR logic with multiple columns involved. 

View 5 Replies View Related

SQL 2012 :: Converting Case Statement To Config Table?

Nov 3, 2015

I have a stored procedure in which we are deriving some flags. So, we used series of CASE statements.

For examples

CASE
WHEN LEFT(CommissionerCode, 3) IN ('ABC','DEF',...) THEN 1
WHEN PracticeCode IN (.......) THEN 1
WHEN (CommissionerCode IN (.....) OR PracticeCode NOT IN (.....) OR .....) THEN 1
ELSE 0
END

I need to put these conditions in config table and generate dynamic sql.

What is the best way to do this? especially, 3rd condition with OR logic with multiple columns involved.

View 2 Replies View Related

T-SQL (SS2K8) :: Rows Into Columns - Remove Duplicates And Variable Rows

Aug 5, 2014

I managed to transpose rows into columns.

;WITH
ctePreAgg AS
(
select top 500 act_reference "ActivityRef",
row_number() over (partition by act_reference order by act_reference) as rowno,
t3.s_initials "Initials"
from mytablestuff
order by act_reference

[code]...

But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..

ref, initials
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW

OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view

ref, init1,init2
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW

View 6 Replies View Related

Turn Columns Into Rows And Rows Into Columns

Jan 24, 2008

I have a report which is a list of items and I display everything about the item. It is great. My report table in the layout tab is simple. Header,Detail,Footer. Each Item has 65 columns. The number of items (rows) vary upon what you want to see. Example data.
Item#, Description, CaseSalePrice, Cost, BottleSalePrice, Discount
123, Grenadine, 100.00, 75.00, 15.50, 2.00
456, Lime Juice, 120.00, 81.00, 17.25, 2.00

There could be 1 item or 4000 items.

What I want to see is.

Item # - 123, 456
Description - Grenadine, Lime Juice
CaseSalePrice - 100.00, 120.00
Cost - 75.00, 81.00
BottleSalePrice - 15.50, 17.25
Discount - 2.00, 2.00

What I am actually doing is running this the top example and saving to excel. Then copying the sheet. Creating a new sheet then doing a paste special transpose and this gives the users what they want to see.

I want to grab that table object in the report layout tab and twist it 90degrees so the header is on the left, detail is in the middle and the footer is on the right. It would be perfect.

The dynamic column need is really the problem here. I never know how many items will be in the report. They all have the same basic information like description and pricing.

I am all out of creative ideas, any help would be appreciated.

View 6 Replies View Related

CASE Throws Conversion Failed When Converting Datetime From Character String Exception

May 1, 2008



Hi All,

I have SQL query as:


SELECT CASE @sortcolumn

WHEN 'TYPE' THEN TYPE_TXT

WHEN 'DATE' THEN CHECKOUT_TS

WHEN 'ACCT' THEN ACCT_NBR

WHEN 'FIRST' THEN FIRST_NM

WHEN 'LAST' THEN LAST_NM

WHEN 'DAYS' THEN DATEDIFF(dd, CHECKOUT_TS, GETDATE())

ELSE DELIVER_ID END

FROM REQUEST

If @sortcolumn has 'DATE' or 'DAYS' it runs fine....any other value, I get an exception "Conversion failed when converting datetime from character string". All columns except CHECKOUT_TS are of type varchar.

Any help would be greatly appreciated.

Thanks,
Shashi

View 3 Replies View Related

Converting Nrows Into Columns

Sep 25, 2007

Hi,

I need to flip around the way data is presented in a table.
I've got columns called 'datafield', and 'datavalue' in one table and I want to move the datavalue into a second table where the column name is the same as the datafield.

Below is the sql to create the first table and populate it, I've also included the SQL to create the second table. I want to move the data from teststart to testfinish. There may also be columns in test finish that aren't included in the data of teststart.


create TABLE teststart
(
ticker varchar(255) NOT NULL,
datafield varchar(255) NOT NULL,
datavalue varchar(255) NOT NULL
)

INSERT INTO teststart
select 'ZTX LN Equity', 'EXCH_CODE', 'LN' union
select 'ZTX LN Equity', 'ID_BB_COMPANY', '112367' union
select 'ZTX LN Equity', 'ID_BB_SECURITY', '1000' union
select 'ZTX LN Equity', 'ID_ISIN', 'GB0008812496' union
select 'ZTX LN Equity', 'ID_MIC_LOCAL_EXCH', 'XLON' union
select 'ZTX LN Equity', 'ID_SEDOL1', '0881249'


CREATE TABLE testfinish
(
ticker varchar(255) NOT NULL,
EXCH_CODE varchar(255),
ID_BB_COMPANY varchar(255),
ID_BB_SECURITY varchar(255),
ID_ISIN varchar(255),
ID_MIC_LOCAL_EXCH varchar(255),
ID_SEDOL1 varchar(255),
Another varchar(255)
)

I'd be grateful for any pointers, thanks.





Sean

View 2 Replies View Related

Converting Columns In An INSERT (was SQL Query)

Jan 17, 2005

I'm trying to create an Insert query and I'm having difficulty in 2 areas:

First, I would like to CAST/CONVERT a single column of the several columns in the tables below. Is it possible to retain the asterisk identifying all columns and single out a particular column to be converted as opposed to writing out each individual column in both the INSERT and SELECT statements? I would like to CONVERT the column "MILL_COST" from VARCHAR(50) to Money.

INSERT INTO ITEM_MASTER
SELECT *
FROM ITEM_MASTER_TEMP

Second, I've tried the following"conversions" in the SELECT statement, to no avail:

CONVERT(Money, MILL_COST) As MILL_COST
CONVERT(Money, CONVERT(Varchar(50), MILL_COST)
CAST(MILL_COST AS Money)

Any pointers much appreciated...

View 2 Replies View Related

Converting Multiple Records Into Columns

Oct 11, 2013

I have a table called DISTRIBUTION_SL in which there will multiple records for each request as shown below in the data.I would like to convert the recepient email into columns and then join with other tables so that the entire request information is seen in one row rathar than multiple rows

Table
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
CREATE TABLE [dbo].[DISTRIBUTION_SL](
[requestid] [numeric](18, 0) NOT NULL,
[rptdis] [char](1) NOT NULL,
[recipnm] [varchar](50) NULL,
[emailadr] [varchar](75) NULL,

[code]....

View 6 Replies View Related

T-SQL (SS2K8) :: Converting Rows Into Column

Apr 18, 2014

It is possible to covert rows into column by using tsql script, see attached file for more details...

CREATE TABLE Table1 (SalesOrder varchar(10), ItemName VARCHAR(100), Price INT, ItemNo int)
GO
INSERT INTO Table1
SELECT '01', 'Camera', 100, 1
UNION ALL
SELECT '01', 'Memory 4GB', 10, 2

[code]....

View 5 Replies View Related

Converting String Into Rows Query?

Sep 19, 2014

I have a string like below

DECLARE @STR VARCHAR(100) = 'AAAAAA~KKKKK~LLLL~AAAA'

i want to convert the string into rows like

AAAAAA
KKKKK
LLLL
AAAA

View 2 Replies View Related

Problem Converting Rows To A String

Jul 23, 2005

I try to accomplish the following:I have two tables which are connected via a third table (N:Nrelationship):Table 1 "Locations"LocationID (Primary Key)Table 2 "Specialists"SpecialistID (Primary Key)Name (varchar)Table 3 "SpecialistLocations"SpecialistID (Foreign Key)LocationID (Foreign Key)(both together are the primary key for this table)Issuing the following commandSELECTL.LocationID , S.[Name]FROMLocations AS LLEFT JOIN SpecialistLocations AS SL ON P.PlaceID = SL.LocationIDLEFT JOIN Specialists AS S ON SL.SpecialistID = S.SpecialistIDresults in the following table:LocationID | Name1Specialist 11Specialist 22Specialist 32Specialist 43Specialist 14Specialist 4Now my problem: I would like to have the following output:LocationID | Names1Specialist 1, Specialist 22Specialist 3, Specialist 43Specialist 14Specialist 4....which is grouping by LocationID and concatenating the specialistnames.Any idea on how to do this?Thank you very much,Dennis

View 5 Replies View Related

SQL CASE Output COUNT Into Two Columns

Aug 27, 2007

Hi Everyone,
 I am trying to do a query where I need to use as little C# as possible to build my gridview.  Basically I have a column called statusID.  There are about 15 options for this column but I only want to count certain ones.  I want to count when statusID = 3 and output that into a column called "fullUnitsUsed" but when the value is 4 or > 13 I want it to count and put the result into a column called "halfUnitsUsed".  I also want it to count based on the month.  To accomplish this I have used CASE and GROUP BY.  This has worked to some extent.  Currently if I COUNT for one month I get the correct number of fullUnitsUsed and halfUnitsUsed used for January.  Unfortunately the query returns 2 records for the month.  The first one has a value for fullUnitsUsed and halfUnitsUsed is NULL, the second record has fullUnitsUsed as NULL and halfUnitsUsed has the correct value.  I was hoping to output one record where both fullUnitsUsed and halfUnitsUsed have data.  My other problem is that if I test for the entire year (which is what this query is supposed to do) there are 5 records returned for each month, 3 of the records have fullUnitsUsed and halfUnitsUsed both as NULL and for the other 2, one has fullUnitsUsed with data and the other has halfUnitsUsed with data and the other column in both records is NULL.  The values for fullUnitsUsed and halfUnitsUsed are counted for the entire year as well, which I only want it to count based on each month.  Below is my query, any suggestions about how to approach this will be greatly appreciated.  If any clarification is needed please let me know.  Again if I could get this to work completely with SQL and not need to use any more C# than I have to it would be preferable.
SELECT People.lastName + ', ' + People.firstName AS fullName, Property.Name, NYSDDSORegion.Description, OpenDays.[month], OpenDays.maxOpenDays,CASE Attend.statusID
    WHEN 3 THEN COUNT(Attend.statusID)
    END AS fullUnitsUsed,CASE Attend.statusID
    WHEN 4 THEN COUNT(Attend.statusID)
    WHEN 14 THEN COUNT(Attend.statusID)
    WHEN 15 THEN COUNT(Attend.statusID)
    WHEN 16 THEN COUNT(Attend.statusID)
    WHEN 17 THEN COUNT(Attend.statusID)
    WHEN 18 THEN COUNT(Attend.statusID)
    WHEN 19 THEN COUNT(Attend.statusID)
    WHEN 20 THEN COUNT(Attend.statusID)
END AS halfUnitsUsed
FROM Attend INNER JOIN People ON Attend.personID = People.personID
                     INNER JOIN Property ON Attend.propertyID = Property.propertyID
                     INNER JOIN NYSDDSORegion ON Property.RegionID = NYSDDSORegion.RegionID
                    CROSS JOIN OpenDays
WHERE (Attend.attendDate BETWEEN '1/1/2007' AND '12/31/2007')
GROUP BY Property.Name, People.lastName, NYSDDSORegion.Description, People.firstName, OpenDays.monthID, OpenDays.[month], OpenDays.maxOpenDays, Attend.statusID
ORDER BY Property.Name, fullName, NYSDDSORegion.Description

View 2 Replies View Related

ORDER BY, CASE, With Multiple Columns

Nov 7, 2003

I'm unable to specify multiple columns in my order by statement if i use a case statement.
Does anyone know why this is, or what syntax would make this work?

Thanks


SELECT ....
ORDER BY (CASE Lower(@SortExpression)
WHEN 'prodname' THEN prodname, prodprice
WHEN 'prodsize' THEN prodsize, prodname
WHEN 'prodprice' THEN prodprice, prodname
Else prodcompany, prodname
END)

View 5 Replies View Related







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