I have the following SQL query that I like to view the out put in horizontal format:
Select ID, First_name, Last_name from ABC
Instead of getting out-put like
Id First_name Last_name
1Jim Smith
2Tom Jones
I like to see the out-put like:
Id1 2
First_nameJimSmith
Last_nameTomJones
Please advice.
Thanks in advance,
Abrahim
From:
Cust | Qstion | Answer
_________________________________
1,1,Bike
1,4,Blue
1,6,No ensurance
2,1,Car
2,3,silver
2,9,ensurance
2,11,Yes
To the following
Cust______1______3______4______6______9______11
__________________________________________________ __________
1_______Bike___________Blue__No ENsur_________
2_______car____silver_________________Ens____Yes"
I have a query that calculates sales by sales person, but it displays horizontally across my query window. Is their a way in SQL Server to have the data display vertically down the window instead?
This is my current query
Code:
Select
count(case when salesman Like 'Geo%' then id else null end) As [George]
,count(case when salesman Like 'Li%' then id else null end) As [Lisa]
,count(case when salesman Like 'Jor%' then id else null end) As [Jorge]
,count(case when salesman Like 'Ri%' then id else null end) As [Richard]
,count(case when salesman Like 'Geo%' then id else null end)+count(case when salesman Like 'Li%' then id else null end) As [Team 1 Sales]
,count(case when salesman Like 'Jor%' then id else null end)+count(case when salesman Like 'Ri%' then id else null end) As [Team 2 Sales]
from sales.southeastregion
Which of course shows the results as such
George --- Lisa --- Jorge --- Richard --- Team 1 --- Team 2
100 50 10 90 150 100
And I want the data to be displayed like
George - 100
Lista - 50
Jorge - 10
Richard - 90
Team 1 - 150
Team 2 - 100
This is SQL Server 2008 if that matters.
TaskID PID task Milestone
83manasi task2
83manasi task3
83manasi task4
Above is the query of the result
I want to show this resultset horizontaly
for that I have to use the cursor such that
@M1=Where Milestone=1
@M2=Where Milestone=2
@M3=Where Milestone=3
and putting this into a temp table with its projectid and taskid.
Plz guide me that how to achiev this exactly
Swati
I have some data which is vertical...I want to create a pivot query in SQL that will give me a result that is horizontal like this. I cannot find a way of doing it without lots of IF or CASE statements?
View 10 Replies View RelatedI've made the view below to calculate the days between 10/05/06 and what ever the current date is. Then multiply the total days by 5 to feed a flash bar chart graphic. The thing I need is how would I convert this result number to currency. I'm not very good with SQL so any replies would be a great help.
SELECT DATEDIFF(DAY, '2006 - 10 - 05', GETDATE()) * 5 AS DayCost
FROM dbo.Graph
Thanks!
I want a report in excel from my SQL database from one table. I wantto create a view and then use DTS package to export it to excelformat.But how can i get the following format in a view?? They should begrouped by date.Date Column 1 column 2 column 329/10/2003 one $30.00 somedatetwo $45.00 somedata2three $67.00 somedata3Total ******** $142.0030/1/2003 blah blah blahblah blah blahand so on . How can i get such a format in a view.Thanks in advance. I can't use SHAPE command in view i guess.
View 1 Replies View RelatedHi
i have created 1 view and in my view i have datetime column adn i have converted it with my custom format by mere convert(column_name,6) and i am done then after when i tried to compare it with my textbox it is taking as string and when i tried to convert my date format to original format it is not allowing me so i have to put 2 duplicate column in my view i am sure there muct be some thing whcih i am not aware
First i have donbe this--> select convert(varchar,columnname,6)-- > 02 May 08
then try to compare it as date with View --> select * from ?<some table> where column_name='TEXT_BOX not desired output
select * from ?<some table> where column_name which in base table='TEXT_BOX i am done
please give me some ideas
Thanks
Parth
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies View RelatedI am looking for a way to create a stored procedure that will show inventory availability. I would like to show the Inventory Name, The Date, and if the inventory is "checked out" using the ID name of the person who has the item.
For example it would look like this:
--------------------------------------------------------------------------------------------------
Inventory Name | 10/24/2015 | 10/25/2015 | 10/26/2015 | 10/27/2015 | 10/28/2015
--------------------------------------------------------------------------------------------------
Laptop | Tom | Tom | Tom | Avail | Avail
Projector | Avail | Avail | Avail | Avail | Bob
Air Card | Bob | Bob | Bob | Bob | Bob
It seems like I want to do a pivot table but there really is no aggregate so I am not sure what to use.
Hi,
I am facing an issue, while exporting report to excel.
I have a report where some legends are being displayed on coversheet using matrix. The contents are fetched from backend.Some of legends have got too much of text while others don't have much.In matrix properties, I checked can increase/decrease to accommodate changes. When report runs in report viewer it shows correctly i.e. it adjusts its width according to contents but when same report is exported to Excel it doesn't happen so. The size of cell is not increasing dynamically, depending upon the data. User needs to expand the cell in order to view the data.
So, is there any option by which, we can implement data wrap functionality for matrix, when report is downloaded in excel format.
Your suggestion will be of great help.
Thanks & Regards,
Kiran Kirdat
Greetings,
I recently started working with a database that uses several views, none of which are indexed. I've compared the execution plans of querying against the view versus querying against the tables and as best I can tell from my limited knowledge the two seem to perform equally. It seems to me that having the view is just one more thing I need to keep track of.
I've done some google searches but haven't found anything that really tells me which performs better, querying the view or the tables directly. Generally speaking which is better?
Thanks in advance for your replies.
If I have the raw data dumped into a big table as following:
Date P R M E Date P R M E Date P R M E Date P R M E
1/1/90 1 2 3 4 1/1/90 2 3 4 5 1/1/90 3 4 5 6 1/1/90 4 5 6 7
...
1/1/05 1 2 3 4 1/1/05 2 3 4 5 1/1/05 3 4 5 6 1/1/05 4 5 6 7
And this table has a repeating block [D, P, R, M,E] 300 times. Is it possible to write a loop query/stored procedures/triggers (or whatever it is) to read each repeating block and stack them on top of each other to insert into another table which has the same structure as following?
Look like this?
Date P R M E
1/1/90 1 2 3 4
...
1/1/05 1 2 3 4
1/1/90 2 3 4 5
...
1/1/05 2 3 4 5
1/1/90 3 4 5 6
...
1/1/05 3 4 5 6
If there is a solution would you please elaborate, example?
Thank you for the help.
shiparsons
Hello all,Thinking about building a new database in the enterprise addition ofsql server and using some horizontal parititioning techniques in orderto accomaodat what will eventually be a monster huge database.Can you share some hard earned experience, gotchas, etc...with me? Wewill be setting up this server on a SAN array that will be made up ofjust one or two huge virtual RAID10 volumes and I am also wonderingabout the wisdom of this? Its simple and should work, but is thatconfig relevant in any way specifically to our plan to partition?
View 2 Replies View RelatedHello frnds......what does mean by Horizontal Partitioning ?
View 4 Replies View RelatedSorry for the earliear question as itself is wrong ?
This is a right questions and what does it mean by ?
how can i use concatenation to query the results horizontally?
example output:
CustomerID=1002, Name=Mr Anderson, City=NY
I have to fetch some rows within a Crystal report. I need to select
from Orderhist a where
order# a = order# cr
and trncde is in 'pwb' 'pdb' 'pbb'
but i need then that trncde as a report field. also the order# cr is a formula field because its a subscript embedded 8 char within a 24 char. this is already available to the cr but can u do the where = on a formula field?
I am testing horizontal partitions to see whether it is a feasible
option for a project. IF I have a composite Primary Key and the
constraint column (a part of the Primary Key) that helps the
partitioned view is defined with DateTime Data Type, select on a restricted set of data through a partitioned view still tries to access all the tables instead of just one table that contains the data. Is this the case or am I
missing something ?
Any help on this is appreciated.
Here is what I am trying to do.
************************************************** **************
CREATE TABLE [dbo].[tst01] (
[Dt] datetime NOT NULL ,
[TID] int NOT NULL ,
[Nm] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tst02] (
[Dt] datetime NOT NULL ,
[TID] int NOT NULL,
[Nm] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tst01] ADD
CONSTRAINT [PK_tst01] PRIMARY KEY CLUSTERED
(
[Dt], [TID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tst02] ADD
CONSTRAINT [PK_tst02] PRIMARY KEY CLUSTERED
(
[Dt], [TID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tst01] ADD
CONSTRAINT [CK_tst01] CHECK (Dt between '11/1/2002' and '11/30/2002')
GO
ALTER TABLE [dbo].[tst02] ADD
CONSTRAINT [CK_tst02] CHECK (Dt between '12/1/2002' and '12/31/2002')
GO
insert into tst01 values('11/1/2002', 1, 'SS')
insert into tst01 values('11/2/2002', 2, 'KK')
insert into tst01 values('11/3/2002', 3, 'DD')
Go
insert into tst02 values('12/1/2002', 1, 'LL')
insert into tst02 values('12/2/2002', 2, 'MM')
insert into tst02 values('12/3/2002', 3, 'GG')
Go
CREATE VIEW vtst AS
SELECT * FROM tst01
UNION ALL
SELECT * FROM tst02
Go
SELECT * FROM vtst WHERE dt = '11/1/2002'
************************************************** *********
When I look at the Execution Plan, it shows that clustered index seek
would be performed on both the tables.
StmtText
---------------------------------------------------------------------------------------------------------------------------------
|--Concatenation
|--Filter(WHERE:(STARTUP EXPR(Convert([@1])<='Nov 30 2002
12:00AM' AND Convert([@1])>='Nov 1 2002 12:00AM')))
| |--Clustered Index
Seek(OBJECT:([test].[dbo].[tst01].[PK_tst01]),
SEEK:([tst01].[Dt]=Convert([@1])) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR(Convert([@1])<='Dec 31 2002
12:00AM' AND Convert([@1])>='Dec 1 2002 12:00AM')))
|--Clustered Index
Seek(OBJECT:([test].[dbo].[tst02].[PK_tst02]),
SEEK:([tst02].[Dt]=Convert([@1])) ORDERED FORWARD)
Thanks,
Sathish
I'm running SQL Server 2008 Standard.I need to create a query that has data from multiple columns (Columns 1-6 with coresponding Date started and Date Completed data) displayed vertically, but also has the column name the preeceeding column to identify it...along with other data (Record number, status).
Record Number, Status, Column Name, Date Started, DateCompleted
1, Open, Column 1, 1/1/2012, 2/1/2012,
2, Hold, Column 2, 1/3/2012, 3/1/2012,
1, Open, Column 3, 2/5/2012, 4/6/2012,
3, Closed, Column 4, 5/10/2012, 7/25/2012,
2, Hold, Column 5, 3/9/2012, 4/1/2012,
1, open, Column 6, 10/10/2012, 12/12/2012,
I'm considering using horizontal partitions to separate my data by year.
For example, SomeTable_2004, SomeTable_2003, etc. This works well for backups, maintenance, etc. because I'm working with 150+ GB of data. I'll be a partitioned view for queries.
However, I'm new at this and have a few questions. I would also like to do partitioned updates or inserts. But I need to make sure that the tables don't use similar primary keys. Does that make sense? I need to make sure that the primary keys from the first table are not used again in the second table.
SomeTable_2003
primary keys: 1,5,8,9,15
SomeTable_2004
primary keys: 2,3,4,10
I don't really care what keys are used on what table, as long as they are different. I have apps that already use this data, and I don't want to change the application logic.
Thanks,
T
id name
--- -----------------
236 SERVICE REQUEST
236 HARDWARE
236 Desktop
336 Loan
id name
-- ----------------------------
236 SERVICE REQUEST/ HARDWARE/ Desktop/Laptop/ Loan
I have a table like this:
weight type factory1 factory2 factory3 factory4.... to factory 150
1kg goods 5.00 5.50 5.20 5.00...
2kg goods 6.00 6.20 6.15 6.30...
3kg goods 4.00 4.50 5.00 4.30...
...
and would like to extract data this way:
producer type weight price
factory1 goods 1kg 5.00
factory1 goods 2kg 6.00
factory1 goods 3kg 4.00
factory1.....
then
factory2 goods 1kg 5.50
factory2 goods 2kg 6.20
and so on for all factories.
I tried with UNPIVOT but it does not allow it (I'm using Navicat 8), saying "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near UNPIVOT...".
Hi all
I dont want to use functions or proceedures but with select itself I want to get the above mentioned result. Can somebody help
SURESH IYER
I have 2 installations:1. W2k with MS SQL 2000 sp2.2. W2003 with MS SQL sp3.There are two databases in all instalations: "maindb" and "userdb". Thetable with data is in maindb.In the 1'st installation I restict user access to all data in table in thisway:- create user's login and user in databases maindb and userdb.- create view in userdb with "where" clause as dbo (dbo is owner of thisview). This clause restict access to data. (create view userdb.dbo.table asselect * from maindb.dbo.table where ...)- add user to group "Public" in maindb.- add role "data reader" to user in userdb.- effect: user can access data only by view, can not access any data inmaindb.I do the same in secound installation:- effect: user can not access data by view - message like this: "userhave not permission to select on maindb.dbo.table"Is this bug in sp3 or in sp2 ?Is there another way to horizontal restrict access data in tables?In Sybase ASE this method (restict by view) works ok. And there is newproperty of ASE 12.5.1 - administrator can define context of login - the"where" clause will be added automatically to any select.Please help me. Thank You for any advice.
View 2 Replies View RelatedI recently came across a database where the data are horizonally partitionedinto 4 tables. I'm not sure if this was a poor design choice, or if it wasdone for valid performance reasons. The schema of the tables are essentiallythe same, it's just that they are named differenly and the columns are nameddifferenlty to differentiate the data from a business usage perspective. Thetables could easily be combined inot one by adding a new colum to theclustered index that would be used to differentiate the business usage. I amtrying to evaluate whether combining the tables would improve performance orif it would be better to leave them the way they are. Many queries that runagainst these tables do not request records from more than one of thetables, which is good. However, there are a number of processes that queryagainst all of the tables on the identical clustered index range. I am notsure exactly how many rows are in the tables but I'm fairly certain theentire database is < 50 GB.
View 13 Replies View Related
Anyone know why the horizontal scroll bar doesnt show up when the data is too large to fit into the given window? The vertical scroll bar will show up but never the horizontal.
Any ideas?
Thanks
I have the following SQL query where i want thease to be populate to GridView, but the Duration field is in Second format, I want it would be in HH:MM:SS format.
cmd = "select subscriber_id as Subscriber_no,,amount,duration from MyTable" ;
Please help me how to format this within the Query to display in GridView.
I have a text box that is used to submit stock symbols that are to be saved in a sql table. The symbols are to be separated by a space or a comma (I don't know which, yet). I want to retrieve the symbols later to be used in a query, but I don't know how to get the symbols in the proper string format for the query, eg
The symbols are stored in the tables as: A B C D
The query string criteria would look like: IN('A', 'B', 'C', 'D')
The IN('A', 'B', 'C', 'D') citeria would be the values in the @Symbol variable in this SPROC
SELECT a_Name_Symbol.Symbol, a_Financials.Revenue
FROM a_Financials INNER JOIN
a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol
WHERE (a_Name_Symbol.Symbol @Symbol)
ORDER BY a_Name_Symbol.Symbol
Is there a slick (ie easy) way to change the contents entered in the text box (A B C D) into IN('A', 'B', 'C', 'D') ?
Thanks,
Paul
select last_name,hire_date,to_char(hire_date,'DAY') DAYfrom employeesorder by to_char(hire_date-1,'d')i wanted to know how the function to_char(hire_date-1,'d') works...its basically a query used to find the day on which a employee washired ,also it requires that the query be sorted by the day of theweek on which the employee was hired.it'll be helpful is someone replies to this
View 2 Replies View Related
Hi
I wrote query to get date from database SQL 2000 server. Sometime I get different date format not MMDDYYYY but number as Julian calendar?? Do you know what wrong with this? How can I wrote SQL so that all date will be MMDDYYYY?
Thanks
Daniel
HI, I'm testing out partitioning for an upcoming project and I'm a little confused over the query plan im generating.
I have a 13 million row table I want to break in to date ranges:
tables are calljrnl,calljrnl_1999 and calljrnl_2000. The calljrnl table contains data for the last six months. one view, calljrnl_view is a union all of these three tables.
alter table calljrnl add constraint dt_constraint check (cj_created_dt >= '2000-10-01 00:00:00')
alter table calljrnl_1999 add constraint dt_constraint_1999 check (cj_created_dt <='1999-12-31 23:59:59')
alter table calljrnl_2000 add constraint dt_constraint_2000 check (cj_created_dt >='2000-01-01 00:00:000')
alter table calljrnl_2000 add constraint dt_constraint_2000_2 check (cj_created_dt <='2000-09-30 23:59:59')
The problem I'm having is that when I query on any date range outside the constraints set on the calljrnl_2000 table, It still wants to scan the calljrnl_2000 table.
I'm assuming this has something to do with how the constraints are structured, but I am not able to find the right combination.
ALL HELP IS GREATLY APPRECIATED!!!!
Hi,
I have a table with 52 million rows which resides on Primary file group in my database. Because of huge number of rows the performance has gone very down and I would like to break the table into parts.
Can anyone suggest me the steps for doing the same and the number of parts that should be made. It is named as Account_Transactions and contains information of Policies in an insurance database.
Rajat