T-SQL (SS2K8) :: Unpivot Sorting Column In Alphabetical Order Automatically

Jan 27, 2015

I am having problem with the unpivot function of sql 2012, i unpivot my column then i get the result that i wanted but the error that i was encountering was the unpivot is automatically sort the column in alphabetically order which is not I desire,

Here is my code

@syear nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

[Code] ....

View 1 Replies


ADVERTISEMENT

Sorting Column For Table - Not Alphabetical Or ID

Nov 26, 2007

Hi, I have a table whose Identifying column is not an integer but rather a manually entered id. (i.e. 106F, 106-09, G11 etc.) When sorted ascending, a G11 will come before a G2 in the list, 106-11 before 106-2, etc.I would like to insert a new column in the database or use some kind of function when sorting the database to ensure that the list in returned in the proper order. Any ideas?Much appreciated... 

View 16 Replies View Related

Display Database Column In Alphabetical Order

Mar 12, 2007

Hello
I know how I can display a list of names in alphebetical order on my website:
Select L as [Last Name]
From  Name_CatEWhere Education = 'yes'Order ByLName ASC
However, to make things a little more orginised I would like to view my database table column in alphabetical order also, but ithie code does not work within my database.
What do I need to change in the following code, to view my database table column in a-z order?
 SELECT LName FROM Name_CatEORDER BY LName ASC
Thanks
Lynn

View 1 Replies View Related

T-SQL (SS2K8) :: Select Only Rows With Alphabetical Character In Column?

Apr 17, 2014

i am working on a small project, that I have found that someone is storing a float as a varchar(). But there are also some actual words in the same column.

I am trying to determine how I can select only the rows with alphabetical characters in that column.

Some of the data is:

1.5008e+015
1.54453e+015
1.51922e+015
1.51922e+015
1.52243e+015

but there is a mix of alphabetical characters in there as well.

1.51922e+015
1.53122e+015
FMCIT
ABCNP
FMCPNG
1.62073e+015
1.6127e+015

I want to be able to select the rows with only the alphabetical characters. There is a huge mix, and I am assuming that every first letter is one of the 26 alphabetical character used. How can I write a query to use a REGEX to select any and all rows that cannot be CAST as a Float? I have nill to no experience using REGEX.

View 9 Replies View Related

Column Sorting In Ascending Order

Nov 27, 2007



Hi

I have Constructed a table

Table Name : MasterEntry

Column Name:
MasterEntryNumber
ServiceName
ServiceDepartment
EmployeeName

MasterEntryNumber is GenerationNumber where ever any entry is happen in table

if put a queries [select * From masterentry Order by MasterentryNumber] is give output in ascending order

e.g MasterEntryNumber has
1
2
3 it give correct ordering

but if i exceed more 10 if i put same queries

Output of queries is
1
10
11
2
3 this is the out of the queries if it exceed more 10 rows

i want queries should be look like this
1
2
3
...... 10

did any have experience on this issue please let me know where i can fix

kinds regards


View 8 Replies View Related

Sorting A Column In Descending Order Which Contains Two Years

May 21, 2014

I am trying to sort this simplied table:

ID - Time
1 2000-2001
2 2002-
3 2001-2003
4 1999
5 2005-2006

I want this as a result:

1999
2000-2001
2001-2003
2005-2006
2002-

Because the "-" means "continues", it the thing is still activated, so if it makes it easier, i could put the today's year afterwards during the query, if it ends with a - ...

Now, simply doing a
SELECT * FROM [table] ORDER BY TIME;

Sorts it "perfectly", apart from the "2002-" is just placed before 2005 and after 2001.

So, of course, it fails on all entries with a leading "-" ...

Right when I clicked "submit", of course, I can simply replace all entries with a time ending with "-", with the todays Year, so at least they will get at the end of the query...

Well, have to do a union, first sorting all without the "-", then sorting all with the "-", and that should be it...

View 5 Replies View Related

Sorting Matrix Column In Random Order

Aug 14, 2007



Hi,

I have a matrix report...the column results
are as follows

Con Std , Con Access, SF Std, SF Acc, Broadband, Pay TV

how would i make the columns appear in the above order when displaying as it is default alphetically sorted...I have tried putting numbers at the front which work till I get to the number 10 which alphetically sorted is next to 1 not 9?
is there a better way off sorting matrix column which have no specific criteria to sort from?

thanks

View 6 Replies View Related

Generate A Database Script In SQL 2005 In Alphabetical Order

Nov 7, 2007

In SQL 2000, when you generated an SQL script for a database, it was logical and the tables in the script were in alphabetical order. In SQL 2005 they are all mixed up. Am I missing something?

Thanks

Peter

View 8 Replies View Related

SQL Server 2005 Database Generating Script DDL In Alphabetical Order

Jun 21, 2007

Using the scripting wizard in SQL Server 2005 database engine, I have been able to script all my DDL out to a flat file which is great; however, when I scripts for instance all views I would like to have the script in alphabetical order by view name, is there a value I can set to accomplish this?



Thanks

View 9 Replies View Related

T-SQL (SS2K8) :: Order By On Date Descending In A Column

Jun 18, 2014

Below SQL gives the results of 2 columns and I need the DepartmentandDate column to be ORDER BY on date desc for that CID. Expected result is in the screenshot(attachment)...

IF OBJECT_ID('Tempdb..#tTable') IS NOT NULL
DROP TABLE #tTable
CREATE TABLE #tTable(CID INT, CDate DATETIME, Dept VARCHAR(25))
INSERT INTO #tTable(CID, CDate, Dept)
VALUES
(111, '2014-01-14 00:00:00.000','B is alphabet'),

[Code] ....

View 6 Replies View Related

T-SQL (SS2K8) :: Change Column Order In Dynamic Pivot?

Sep 17, 2014

I am creating dynamic pivot and my column order is as below

[2015-02],[2015-04] [Prior] ,[2014-08],[2014-11]

but i want to display as below:

[Prior],[2014-08],[2014-11],[2015-02],[2015-04]

View 1 Replies View Related

T-SQL (SS2K8) :: Pivot And Unpivot In Same Query?

Feb 15, 2014

I have below table and within same query i need pivot and unpivot.

create table #temp(name1 varchar(10),name2 varchar(10),name3 varchar(10),month date,emp1 int,emp2 int,emp3 int,emp4 int)
insert into #temp values ('a','b','c','1-1-2013',1,2,3,4)
insert into #temp values ('a','b','c','1-2-2013',11,20,30,40)
insert into #temp values ('a','c','c','1-1-2013',22,30,80,40)
insert into #temp values ('a','c','c','1-2-2013',28,34,39,30)
select * from #temp

Now i need output in below format

name1,name2,name3,Emp,jan-13,feb-13
a,b,c,emp1,1,11
a,b,c,emp2,2,20
a,b,c,emp3,3,30
a,b,c,emp4,4,40
a,c,c,emp1,22,28
a,c,c,emp2,30,34
a,c,c,emp3,80,39
a,c,c,emp4,40,30

View 4 Replies View Related

T-SQL (SS2K8) :: Comparing Columns In Unpivot Table

Nov 21, 2014

SET NOCOUNT ON;

IF OBJECT_ID('dbo.TBL_SAMPLE_DATA') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DATA;

CREATE TABLE dbo.TBL_SAMPLE_DATA
(
ROW_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,Airline VARCHAR(50) NOT NULL

[Code] ....

I have to compare the Aircraft1 and Aircraft1_unsub based on condition as below:

[case
when Aircraft1='N' or Aircraft1_unsub='Y' then 0
else 1
end ]

Based on the comparision output i have to update the main table with the outputvalue for each Aircraft based on the Airline

update t set t.Aircraft1=outputvalue
from main_table t
inner join TBL_SAMPLE_DATA s
on t.Airline=s.Airline

update t set t.Aircraft2=outputvalue
from main_table t
inner join TBL_SAMPLE_DATA s
on t.Airline=s.Airline

View 7 Replies View Related

T-SQL (SS2K8) :: Case Query - Unpivot Function

May 6, 2015

I've this result from my 'case' query;

Jan Feb Mar April
1 2 3 4

I want ;

Month Value
JAN 1
Feb 2
Mar 3
April 4

View 3 Replies View Related

DB Design - Alphabetical Column Names?

Dec 13, 2004

I have been discussing with some coworkers whether or not it makes sense to invest the time to alphabetize the column names in our tables (aside from the PK and possibly FK's that could be listed first). My reasoning for doing so would make it much easier to scan the list of columns in a table that I was not familiar with to see if it contained a particular column (i.e. meeting_id). I was just wondering if this is common at all in our industry for new DB design (I realize why legacy systems would not be ordered in this way). I remember seeing MSFT designed their tables this way when Site Server first came out.

An argument was made that when you add a new column you would insert it into the appropriate location and in order to make this happen, EP needs to create a temp table, move the data and consequently lock the table until the data has been transferred. While this is correct, I do not see this negative as outweighing the positive experience achieved by scanning a list of fields in a table in a more orderly fashion.

Thoughts?

View 14 Replies View Related

T-SQL (SS2K8) :: Join Or Pivot / Unpivot For Mismatch Dates

Jul 31, 2014

I have a table which uses multiple joins to create another table but it turns out that the effective_date which is used in the join to match row together does not work all the time since some of the dates for the effective date column are out of sync meaning records that show data as missing even when the other table contains the data. I try the SQL script below but it returning 6 rows instead of 3–

select t2.[entity_id]
,t2.[effective_date]
,[company_name]
,[last_accounts_date]
,[s_code]
,[s_code_description]
,[ineffective_date]

[code]....

View 3 Replies View Related

T-SQL (SS2K8) :: Unpivot Syntax And Multi-part Identifiers

Jan 29, 2015

This query is the first time I am using the Unpivot syntax and I am coming across a problem. When I try to unpivot my data, I get the following statement:

"Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Table3.DocketId" could not be bound."

What is the cause of this issue?

Select
Table3.DocketId,
UP.AssignmentType,
Up.AssignedStaff
From
(
Select distinct
Table2.DocketId,

[Code] ....

View 3 Replies View Related

How To Set The Dynamic Column Name In Unpivot SQL

Apr 10, 2008

Dear All,

We are trying to unpivot the columns into rows but the colunms are changing dynamically, want to know how to set the dynamic value or variable in unpivot query, query is mentioned below:

declare @aw Varchar(100)
set @aw = '1990,1991'
SELECT [name], [year], [data]
FROM (SELECT * FROM t1 where [name] != 'name') p
UNPIVOT
([data] FOR [year] IN
(@aw)
)AS unpvt

The above query while executing geting error message "Incorrect syntax near '@aw'"

please can anybody solve this issue.

Thanks,
Syed

View 6 Replies View Related

How To Set The Dynamic Column Name In Unpivot SQL

Apr 10, 2008

Hi All,
We are trying to unpivot the columns into rows but the colunms are changing dynamically, want to know how to set the dynamic value or variable in unpivot query, query is mentioned below:

declare @aw Varchar(100)
set @aw = '1990,1991'
SELECT [name], [year], [data]
FROM (SELECT * FROM t1 where [name] != 'name') p
UNPIVOT
([data] FOR [year] IN
(@aw)
)AS unpvt

The above query while executing geting error message "Incorrect syntax near '@aw'"

please can anybody solve this issue.

Thanks,
Syed

View 4 Replies View Related

Can You Unpivot On Multiple Keys With The Unpivot Transform

Oct 17, 2006

Hi all,

I have more than 1 set of columns I'm trying to normalize, can an unpivot transform handle 2 unpivots around DIFFERENT keys in the same transform?

If not, would the suggestion be unpivot into temp tables for whatever needs normalizing, than run an update query to generate the row that is desired?

Thanks in advance.


Mark

View 2 Replies View Related

T-SQL (SS2K8) :: Sorting Record By Use

Aug 5, 2014

I need to write SP which will call from front End to populate dropdown, But in dropdown uppermost value should be frequently used.

For example, My SP Select CountryID,Country Columns where i am displaying Country in dropdown. Now first time it will sort by ascending, next time if user selects US then US should be top and rest things after that.

Now If user selects Canada 3 times then result should sort as Canada,US, and rest countries.

if again US Selects 7 times then result should display as US,Canada,........

In short need to sort depending used previously. i.e. most popular first and so on......

View 6 Replies View Related

Sorting Date/timestamp Is Out Of Order

Jan 18, 2005

I have a problem with an Order By sort on a SubmissionDate column in my SQLSERVER DB.

I am inputing a timestamp in this format into the column above: 1/18/2005 11:03:19 AM

Problem is, once I sort this column in DESC order to return the results to a datalist dates with a time like this:

1/18/2005 1:03:19 AM

get placed out of place (lower on the return in DESC/higher on the return in ASC). I am assuming this is happening because it reads 1 as coming before 11 instead of after like it is with time. If this was in 24 hour format this wouldn't be a problem I guess because 1PM would be 13, so that is after 11.

Anyone know what I can do to get this sorted correctly?

View 3 Replies View Related

Field Not Sorting In Ascending Order

Dec 30, 2005

Hi, I've created a website usiing asp.net and all the data are stored in sql front. All the item are sorted in ascending order except one record. The correct order should be MP61, MP100, MP200, but this record is retrieved as MP100, MP200, MP61. If the coding is wrong, all the order displayed is not in ascending order. We have hundreds of  items, but why it happens to this particular record? Can anyone help? Thanks in advance

View 3 Replies View Related

Sorting The Data In Ascending Order .

Aug 16, 2002

Hi ,

Hopw can i sort the data in the ascending order so that i can see the date of my log files imported at the top of my table .

Right now my log files get imported and appended in the tables but they get appended in the descending order at the bottom of the last data .

Is there any way to sort the data so that i can see it in the reverse way when i open my tables ?

Many Thanks .

Anita.

View 1 Replies View Related

Sorting Cursor Output WITHOUT Using Order By

Mar 1, 2004

Hi,
I have a situation where I need to sort the output of a cursor. But since the sort criteria are rather complex, I am NOT able to use the Order By clause directly with the cursor definition statement.

Hence, I need to have a solution where I will use a dummy (calculated) field within the CURSOR and I want the output of this cursor sorted by the dummy field that I calculated within the cursor itself.

Please let me know the different possibilities in this scenario.

Thank you in advance
Raj

View 3 Replies View Related

ORDER BY NOTESDATE DESC, CREATEDBY ASC --Not Sorting Properly

Mar 15, 2008

I am using sql statement like SELECT CREATEDBY,FIRSTNAME,BUSINESS,NOTES,NOTESDATE FROM BUSINESS ORDER BY NOTESDATE DESC, CREATEDBY ASC
But NotesDate is sorting descending order, but only sorting based on the date and month not on year
Please help me

View 4 Replies View Related

How To Change Column Names Dynamically In UNPIVOT TASK

Apr 10, 2008

Dear All,

We are using UnPivot task to convert the columns into rows using the Excel File as source. But the Excel file column names are changing frequenly sometimes its having only 4 columns sometimes its 10 columns.

Everytime we are checking and unchecking the column list in Unpivot Task.

can anybody help us to solve this issue that Unpivot task should take the column name dynamically.

Thanks,
Syed

View 1 Replies View Related

Transact SQL :: COLUMN NAME Conflicts With Type Of Other Columns Specified In UNPIVOT List

Jul 31, 2015

I am trying to convert all columns to rows in sql, but giving an error.

SELECT Employee, Orders
FROM
(SELECT CisId, [Z_Id], [ModuleType]
FROM CIS) p
UNPIVOT
(Orders FOR Employee IN
(CisId, [Z_Id], [ModuleType])
)AS unpvt;

Error: The type of column "Z_Id" conflicts with the type of other columns specified in the UNPIVOT list.If I remove "Z_Id" from selection then giving same error for ModuleType also.

View 6 Replies View Related

Default Sort Order When Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go


insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

select top 10 * from a order by aName
Result is:
11Bank of abcde
10Bank of abcde
9Bank of abcde
8Bank of abcde
7Bank of abcde
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:

Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.

So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 14 Replies View Related

Default Sort Order When The Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go

insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

select top 10 * from a order by aName
Result is:
11 Bank of abcde
10 Bank of abcde
9 Bank of abcde
8 Bank of abcde
7 Bank of abcde
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users.
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 5 Replies View Related

Sorting Column

May 14, 2008

Hi all,

i have this column name "Effective Date". the data inside it is like this:




01-Aug-2006

01-Jan-2007

01-Jun-2006

01-Mar-2004

01-Nov-2003

01-Oct-2005

02-Oct-2007

05-May-2006

08-Oct-2003

1-Agustus-2005

10-Feb-2002

10-Jan-2005

10-Mar-2003

11-Feb-2008

12-Dec-2006

12-May-2008

14-Nov-2006

15-Jun-2004

19-Jun-2007

22-Mar-2007

23-Apr-2007

23-Jan-2008

26-Feb-2008

27-Jan-2005

27-Mar-2007

27-Sep-2007
How to sort the column? i've using Order by [Effective Date], but it doesnt work.

please help.

thanks,
Addin

View 8 Replies View Related

T-SQL (SS2K8) :: Sort Data By Using ORDER BY

Mar 7, 2013

When We sort the data by using ORDER BY , which sorting algorithm method will be used by SQL optimizer?

1) Bubble Sort
2) Quick Sort
3) Merge Sort
4) Heap Sort
5) Insertion Sort

View 9 Replies View Related

T-SQL (SS2K8) :: Partition And Order Records

Oct 8, 2014

I have the below requirement:

1. Group records according to docno column.
2. Records will sort in desc order. (According to date1 column)
3. In date1 column if more than one date is same than we ll consider the date2 column.
EX: 2008-04-30 00:00:00is same here so sorting will happen based on Date2 column. So internal sorting should happen instead assigning random values.
4. Number column is the expected output column.

docnodate1 date2 Number
d1 2008-08-25 00:00:00 2009-09-08 11:23:41 1
d1 2008-04-30 00:00:00 2008-09-08 14:40:53 2
d1 2008-04-30 00:00:00 2008-09-08 14:29:43 3
d1 2008-04-30 00:00:00 2008-09-08 13:30:04 4

[Code] ....

View 5 Replies View Related







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