Price
-----
777
888
999
777
888
999
777
888
999
(pretend these columns are side by side)
======================
I need to make a query to:
SELECT PRICE AS 'PRICE1' WHERE PRICELIST = 1
AND SELECT PRICE AS 'PRICE2' WHERE PRICELIST = 2
AND SELECT PRICE AS 'PRICE3' WHERE PRICELIST = 3
the output that i want is:
PRICE1
------
777
777
777
PRICE2
------
888
888
888
PRICE3
-----
999
999
999
(pretend these columns are also side by side)
Using SQL Server 2000. How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.
Table 2 contains meta-data for Table 1, specifically, it has two columns: column, meta-data
Table 2’s data is something like:
column,metadata col_1,name col_2,email col_3,city
(Hopefully, my description of the design makes sense….basically; Table 2’s data describes what’s in each column of Table 1).
So, the question, if I want to write a ‘SELECT’ on Table 1, how can I use the data in Table 2 as aliases (or column) headers.
I’m currently going down the path of building dynamic SQL statements in T-SQL….but before I get too far, wanted to vet this idea here (it’s always been a fantastic resource for me)
select col1, col2, col3, col4, col5,..... , (select col99 from tab2) as alias1 from tab1 where <condition> order by case @sortby when 'col1' then col1, when 'col2' then col2, when 'col3' then col3, when 'col99' then col99 end
when i execute the above query it gives me the following error message.
Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'col99'.
I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)
I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.
Hi,Would like to know the performance differenece between Multi-columnIndex vs Single Column Indexes. Let's say I have a table with col1,col2, col3 along with a primary key column and non-indexed columns.In queries, I will use col1, col2, and col3 together and some timesjust one or two of these three columns. My questions is, should Icreate one index contains col1, col2, and col3, or create 3 seperatedcolumns. I.e. each column has its own index. Any performancedifference?Thanks a lot.
I have 6 columns: Column1a, Column1, Column2a, Column2, Column3a, Column3
I need a statement that will go through the whole table as follows: For each row, if column3 is not null then return column3 as columnB and column3a as ColumnA. If column3 is null then if column2 is not null then return column2 as columnB and column2a as columnA. If column3 & column2 is null then return column1 as columnB and column1a as columnA.
I have a [TestTable] table with three rows. The pair of columns [Test1] and [Test2] are id, the [Test3] is a data column. First, I get a table variable with list of id pairs. Next, I would like to update the rows of that ids. However, I have not found the elegant way how to do it. For one column it is simple, just IN clause, which does not work (or I could not find how) for multi-columns. Does someone have a hint?
Thanks,
Martin
Note: The example bellow is dummy; on the other hand, I hope it shows the important points. Please, do not beat me on syntax errors.
Code Block DECLARE @MyTableVar table( Test1 int NOT NULL, Test2 int NOT NULL );
SELECT [Test1],[Test2] INTO @MyTableVar FROM [TestTable] WHERE [Test3] = '%dd%';
UPDATE [TestTable] SET [Test3] = [Test3] + 'ds' WHERE ([Test1], [Test2]) IN (SELECT [Test1], [Test2] FROM @MyTableVar);
What I need to do is to create 3 columns with 3 different aliases from the same table that will return all the values during the following conditions:
when pricelist = 1
when pricelist = 2
when pricelist = 3
pricelist -------- 1 2 3
Price -------- 912 -- (linked with 1) 234 -- (linked with 3) 56 -- (linked with 2) 3245 -- (linked with 3) 234 -- (linked with 1) 65 -- (linked with 2)
these 2 columns are in the same table^^
so what i want my query to generate is:
Price1 -------- 912 234
Price2 -------- 56 65
Price3 -------- 234 3245
Any help is apprecieated, thanks
if the above does not make sense to you maybe this will: "can you make 3 aliases of the same column and only display the rows inside each column where pricelist = 1 for the 1st alias... where price = 2 for the 2nd alias...where pricelist = 3 for the 3rd alias"
I have a 2 coulmns report ,one group ,when I print the report,it does not paging correctly.For example,suppose every column can display 20 rows, if every group has 20-40 rows ,the print is ok,it can paging correctly,but if one group has 10 rows or more than 40 and less than 60,the two groups will print on one paper,how can I print the every group on different paper?
Hi, I have created a multi column report (2 columns), which is working fine. However, I need to have a chart on the same report, but it needs to be the width of the page, and not just the width of one column.
If I could put the chart in the report footer / header that would be perfect, but obviously I can't!
Has anyone else come across this, and any ideas / suggestions you have would be appreciated!
I am trying to get a simple list to wrap and print in multiple columns on my report. I have indicated 3 multi-columns in the report properties and see the multiple columns in the layout, but I must need to do something else because the report is still printing in one column to multiple pages. This should all fit on 1 page if the list will wrap or snake as it says it the directions for using multi-column reports.
Hi, I have got a column which should multi languages data(Chinese,English etc). Source for this data is Excel. I have kept this column DataType as NVARCHAR but Chinese data is shown as 'Boxes'.But when i copy this and paste on Query pane i get proper data.
FROM property, address, ppi, code_table state, code_table country, code_table prop_role
WHERE property.id = ppi.property_id AND ppi.address_id = address.id AND (property.eff_to_date IS NULL OR property.eff_to_date >= getdate()) AND (ppi.eff_to_date IS NULL OR ppi.eff_to_date >= getdate()) AND ppi.eff_from_date <= getdate() AND state.id = address.province_state_cd AND country.id = address.country_cd AND prop_role.id = ppi.prop_role_cd AND prop_role.code_table_cd = 'OWNER' AND property.pact_code <> 'PERS'
hi i have over 200 tables with all same column and data type locate all over the server(20)different database. so i have table call Tname to stored all the link like (databasename.dbo.tablename) so my job will call the Tname table to use cursor to insert records. but the problem is there's one or more table's column name K datatype has been modify by someone else. so is it cause the job fail. if there a way or SQL statment that i can use Table Tname to see which one is missing column name K? and is there a way i can print out all the datatype and len for all the table column k? please help thanks
SELECT [Hourly] ,count(case when C.Documenttype=1 THEN '1' END) as EDI ,count(case when C.Documenttype=2 THEN '2' END) as SI ,count(case when C.Documenttype=1 THEN '1' END) + count(case when C.Documenttype=2 THEN '2' END) as GrandTotal
I am working with a table that has a column which stores multiple data/values that are comma separated.
I need to be able to query that table and get those rows where the values in that column match a pre-defined search list.
I was thinking of somehow trying to take the search list and convert it to a table(temp or a cte) and then JOIN to the table.
however, since the column may contain multiple values, i would need to parse/separate that first. I am not sure how to parse and then join to a list (if that is even the best way to solve this) to only get the rows where the search column contains one or more of the items we're looking for.
Below is some sample data:
Declare @BaseTable table (PKCol int, Column2Search varchar(2000)) Insert into @BaseTable (PKCol, Column2Search) Select 1001, 'apple,orange,grapefruit' UNION ALL Select 1002, 'grapefruit,coconut' UNION ALL
I have a multi-column report with a page header that spans the width of the report. Whenever I close the report and reopen it, the header is changed to the width of the column. This isn't a real big deal because I deploy it with the header across the whole page; but once in a blue moon I accidentally hit the F5 key (which I am used to using for refresh in other environments I work in) and the report gets redeployed with the messed up header. Is there anyway to stop RS from automatically adjusting the page header?
And is there anyway to disable the F5 key from deploying the reports. I do use it to build and run windows apps, and don't want to change that, but I don't want to deploy 80+ reports when I accidentially hit it.
I have been pulling my hair trying to figure out what the guys at microsoft were thinking when creating the ADOX library. I have an access table that is syncronized with a SQL server. The table has a primary key with two columns [User] and [Program]. The SQL Server has both columns in as the primary key columns and I have a syncronization mechanism that is responsible for several things, one of which is to recreate the Access data structure. All works well for all tables except this one. I have tried to create the multi-column key in several ways, none that worked. Let me show you what I am doing:
Does anyone know of a neat way of aliasing tables in MS SQL Server 2000? I have a table [X] and I want to refer to it (in TSQL statements, stored procedures, etc.) by two names, say [X] and [Y]. There should be only one copy of the table's data.
I could handle this in my site logic (ie convert [Y] to [X]), but it would be really clean to handle this in the database.
trying to write a query from 2 tables and join them only they share a column name of the same name.I can define both aliases seperatrly but get an error when trying both,
select case_HDR.case_nbr as "test" Case_DTL.case_nbr as "test2" From Case_HDR, case_dtl
I need to join them also but trying to work in stages.
Hi all,A (possibly dumb) question, but I've had no luck finding a definitiveanswer to it. Suppose I have two tables, Employees and Employers, whichboth have a column named "Id":Employees-Id-FirstName-LastName-SSNetc.Employers-Id-Name-Addressetc.and now I perform the following join:SELECT Employees.*, Employers.*FROM Employees LEFT JOIN Employers ON (Employees.Id=Employers.Id)The result-set will contain two "Id" columns, so SQL Server willdisambiguate them; one column will still be called "Id", while theother will be called "Id1." My question is, how are you supposed toknow which "Id" column belongs to which table? My intuition tells me,and limited testing seems to indicate, that it depends on the order inwhich the table names show up in the query, so that in the aboveexample, "Id" would refer to Employees.Id, while "Id1" would refer toEmployers.Id. Is this order guaranteed?Also, why does SQL Server use such a IMO brain-damaged technique tohandle column name conflicts? In MS Access, it's much morestraightforward; after executing the above query, you can use"Employees.Id" and "Employers.Id" (and more generally,"TableNameOrTableAlias.ColumnName") to refer to the specific "Id"column you want, instead of "Id" and "Id1" -- the"just-tack-on-a-number" strategy is slightly annoying when dealing withcomplex queries.--Mike S
