Transact SQL :: Special Names For Columns In Cross Tabs?
May 7, 2015
While looking forward to design a multi-columnar cross-tab query I am anxious to know if there could be a way to change the default names of the pivot columns? In other words for the query like the following can there be a way to apply anAS type command to reflect some other names, instead of having the four dates in heading? Something like Month_A, Month_B?
SELECT * FROM
(SELECT
X.REP_DT,
X.CUST_ID
AMOUNT_1
FROM
X) P
PIVOT (SUM(AMOUNT_1) FOR REP_DT IN ([2014-12-31], [2015-01-31], [2015-02-28], [2015-03-31])) PVT_01
View 3 Replies
ADVERTISEMENT
Feb 26, 2015
I am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:
select [uplift specialty], [member po],[practice unit name], [final nomination status]
,[final uplift status], [final rank], [final uplift percentage]
,practiceID=row_number() over (partition by [practice unit name] order by Metricname)
,metricname,Metricvalue, metricpercentilerank
[code]....
Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:
GDR (nvarchar(255), null)
GDR Percentile Rank (nvarchar(255), null)
GDR PGS (nvarchar(255), null)
GDR Rank Number (nvarchar(255), null)
PMPM (nvarchar(255), null)
[Code] ....
These are imported from an Excel Workbook so that's why all the columns with spaces for now.
View 9 Replies
View Related
Apr 17, 2004
I want a query which is used in sql server like access query
TRANSFORM Sum(Q_DayBook.Debit) AS SumOfDebit
SELECT Q_DayBook.Purticular, Sum(Q_DayBook.Debit) AS [Total Of Debit]
FROM Q_DayBook
GROUP BY Q_DayBook.Purticular
PIVOT Q_DayBook.CDate;
View 1 Replies
View Related
May 12, 2015
I am able to get a list of columns in a table from the query I have written shown below:
select sc.name ColumnNames,st.name TableName from sys.columns sc inner join sys.tables st on sc.object_id=st.object_id
order by st.name
But I am looking for the resultset with the format below:
TableName Columns
employee employeeid,employeename,employeesalary
order orderid,address,price
View 2 Replies
View Related
May 28, 2015
I had to re-image my computer and reinstall SQL 2014. Now, when I open a table (edit top 200 rows) a tab opens (of course). But if I do not hit something like Show Criteria Pane and try to open another tab the first tab is replaced by the second table's tab.
How do I keep both open before hitting Show Criteria Pane?
View 2 Replies
View Related
Apr 21, 2015
Using below script to export the select statement result to .xls
declare @sql varchar(8000)
select @sql = 'bcp "select * from Databases..Table" queryout c:bcpTom.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
But result is not exporting in seperate tabs, all 4 column details are exporting in single cell.
how to export the data in columns to separate tabs in excel.
View 2 Replies
View Related
Jul 21, 2015
I would like to know how can i only get the characters before the special character?
For example if the mail id is The.Champ123@gmail.com i need to extract The.Champ123 and if the mail id is TheChamp@gmail.com I need to get TheChamp.So basically i would like to get the characters from the string before '@' character.
View 14 Replies
View Related
Jun 20, 2015
I am creating a key-wording module where I want to search data using the comma separated words.And the search is categorized into comma ',' and minus '-'. Take a look on the example what I exactly want to do is
I have a main table name `tbl_main` in SQL
AS_ID KWD
1 Man,Businessman,Business,Office,confidence,arms crossed
2 Man,Businessman,Business,Office,laptop,corridor,waiting
3 man,business,mobile phone,mobile,phone
4 Welcome,Greeting,beautiful,bride,celebration,wedding,woman,happiness
5 beautiful,bride,wedding,woman,happiness,mobile phone,talking
6 woman,girl,Digital Tablet,working,sitting,online
7 woman,girl,Digital Tablet,working,smiling,happiness,hand on chin
If serch text is = Man,Businessman then result AS_ID is =1,2
If serch text is = Man,-Businessman then result AS_ID is =3
If serch text is = woman,girl,-Working then result AS_ID is =4,5
What is the best why to do this?
View 2 Replies
View Related
May 18, 2015
I would like to write a SQL to extract a text after certain _ special character.Example : Relationship_Person_Type_Aunt
View 9 Replies
View Related
Aug 21, 2015
query that checks to see if a variable contains special characters; except for hyphens, periods, and accents?
View 3 Replies
View Related
Sep 18, 2015
I want to replace special characters and space except '-' Tried with Pathindex and function but not getting expected results.
Ex: Input kjkdjfdf-234#kei$ ewiw
output: kjkdjfdf-234keiewiw
View 6 Replies
View Related
Jul 28, 2015
I have 2 tables, OBJECTS and LINKS both have common field(OBJECT_ID).
I need to update certain records in table OBJECTS only if they meet certain criteria in table LINKS.
How do I go about doing this..???
View 8 Replies
View Related
Jun 8, 2015
I'm trying to replace special characters in SQL SERVER and all the solutions for this RDBMS that I found, it uses loops and the source of my data it's in Oracle. in ORACLE and they use REGULAR EXPRESIONS to solve it..Do you know what its the better option to replace special characters? Using loops in SQL SERVER or REGULAR EXPRESSIONS in ORACLE ?
View 5 Replies
View Related
Jul 10, 2015
If I Have a table like
Id(identity), PupilPersonId, EducationTypeId,VehicleTypeId,EducationDate, EducatorId,Canceled
661187 9242382 2 1 2015-07-07 00:00:00.000 O_2 False
661183 9242382 2 1 2015-07-08 00:00:00.000 O_2 False
661186 9242382 1 1 2015-07-08 00:00:00.000 O_2 False
661178 9242382 2 1 2015-07-10 00:00:00.000 O_2 False
661185 9242382 2 1 2015-07-10 00:00:00.000 O_2 False
The result I want is the unique rows from columns:
PupilPersonId, EducationTypeId,VehicleTypeId AND there MAX EducationDate
SELECT er1.* FROM EducationResult er1
INNER JOIN
(
SELECT
er.PupilPersonId, er.EducationTypeId, er.VehicleTypeId, MAX(er.EducationDate) as EducationDate
[Code] ....
I like to know is there another approach with CTE and or Cross Apply I can use instead?
View 5 Replies
View Related
Jul 28, 2015
I have noticed rather strange behaviour of EXECUTE AS and REVERT sequence during the cross database calls which appear to be a bug. I tested this issue on developer edition of SQL Server 2012
Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
May 4 2015 19:11:32
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
This issue causes problems in SSISDB where similar piece of code appears in [catalog].[start_execution] and some other scripts in the [internal] schema. This was previous discussed in [URL] The following script illustrates the issue:
USE [master]
GO
CREATE DATABASE [Test1]
GO
CREATE DATABASE [Test2]
GO
-- Set Database to Trustworthy to allow cross database connection
ALTER DATABASE [Test1] SET TRUSTWORTHY ON;
GO
ALTER DATABASE [Test2] SET TRUSTWORTHY ON;
GO
USE [Test2]
GO
CREATE PROCEDURE [dbo].[TestContext]
AS
SELECT 'EXECUTION CONTEXT BEFORE EXECUTE AS CALLER', SUSER_NAME(), USER_NAME();
[code]....
View 8 Replies
View Related
Dec 4, 2013
How to get the required result in SQL 2012
Create table DBInfo (Path varchar (500))
Insert into DBInfo values('/Data Sources')
Insert into DBInfo values('/Data Sources/SALES')
Insert into DBInfo values('/PRODUCTION')
Insert into DBInfo values('/PRODUCTION/SERVICE')
Insert into DBInfo values('/PRODUCTION/SERVICE/MAINTENANCE')
Insert into DBInfo values('/PRODUCTION/SERVICE/LOGISTICS')
My Expected Output
Column1,Column2,Column3
Data SourcesNullNull
Data SourcesSalesNull
PRODUCTIONNullNull
PRODUCTIONSERVICENull
PRODUCTIONSERVICEMAINTENANCE
PRODUCTIONSERVICELOGISTICS
View 1 Replies
View Related
Jul 1, 2015
Part 1: When there is ~ (tilde) and has any value after it then it goes into a new row and duplicating the other columns like the facility in the screenshot attached and new column having the sequence.
Part 2: When there is ^ (Caret) its a new column irrespective of a value present or not
CREATE TABLE [dbo].[Equipment](
[EQU] [VARCHAR](50) NOT NULL,
[Notes] [TEXT] NULL,
[Facility] [VARCHAR](50) NULL)
INSERT INTO [dbo].[Equipment] ([EQU] ,[Notes] ,[Facility])
SELECT '1001','BET I^BOBBETT,DAN^1.0^REGULAR^22.09^22.090~BET II^^^REGULAR^23.56^0~','USA' union
SELECT '998','BET I^JONES, ALANA^0.50^REGULAR^22.09^11.0450~BET II^^^REGULAR^23.56^0~','Canada' UNION
select '55','BET I^SLADE,ADAM F.^1.5^REGULAR^27.65^41.475~','USA'
SELECT * FROM dbo.Equipment
I created the table in excel and attached the screenshot for a clear picture as to what is required. I use text to Columns in excel to achieve this not sure if there is anything similar in sql.
View 2 Replies
View Related
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
Apr 29, 2015
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
View 14 Replies
View Related
Mar 19, 2004
I need a querry to get all columns names.
thanks
View 4 Replies
View Related
May 28, 2004
Hey,
I'm creating registration form.
To show fields names I thought to read columns names.
It's ok if columns is named like "Name", "Age" etc.
But if the columns is named [Country, Address, PostCode] then, I think, it can course some problems. Am I right?
First problem I thought about - changing database in the future (Now MS SQL 2k to MySQL etc.)
Is this the only problem?
To solve this I think using table which store syscolumn names as user defined columns names.
My system is speed critical and using this I would get less performance.
Which way should I go?
Case saving columns names in table, how to generate safe column name from user specified name, which can have special charters.
Thanks
View 6 Replies
View Related
Oct 16, 2004
Hi, I need do get a columns names from a table? How to do this in pure SQL? I thought about creating a stored procedure or user function with a result of a string ( col1name,col2name ....) I do not know how to count the number of columns in a specyfied table? Any help would be appreciated. Thanks in advance. magicxxxx
View 2 Replies
View Related
Oct 30, 2015
I'm struggeling a bit with this one, goal is to show the first 3 characters of the name and replace the remaining characters with *** stars. Also the number of stars needs to match the lenght of the name as in the example above.I've tried:
REPLACE([ColName],
substring([Colname],
4,
LEN([colname]
)),
'*')
View 5 Replies
View Related
Jul 6, 2015
I am trying to find the Table and column names from the below.
Is there a way i can get table name and column name from query_plan column?
SELECT TOP 100 text, query_plan,cp.plan_handle
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
WHERE objtype='Adhoc'
View 5 Replies
View Related
Jul 21, 2015
SELECT TOP 100 text, query_plan, cp.plan_handle, qs.last_execution_time
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
JOIN sys.dm_exec_query_stats qs ON cp.plan_handle = qs.plan_handle
WHERE objtype='Adhoc'
I have below output:
Text Query_plan Plan_handle Lst_execution_time
Select id,name from person
<Showplan... dshkkgdaHqrqe13232423 2015-07-21 10:50:22.713
Update customer set Cid=3 where name='abc'
<Showplan... poasfvrqe13232423 2015-07-21 10:16:22.500
delete from orders where ORid=8
<Showplan... 2ase2423 2015-07-21 10:10:22.710
Select 1,2,3,4,5 from num
<showplan afqfqfq 2015-07-21 10:10:22.710
I am looking for
Text Query_plan Plan_handle Last_execution_time TabName colname
Select id,name from person <Showplan... dshkkgdaHqrqe13232423 2015-07-21 10:50:22.713
Person Id, name Update customer set Cid=3 where name='abc'
<Showplan poasfvrqe13232423 2015-07-21 10:16:22.500 customer Cid, name
delete from orders where ORid=8 <Showplan... 2ase2423 2015-07-21 06:10:22.710 Orders ORid
Select 1,2,3,4,5 from num <showplan afqfqfq 2015-07-21 10:10:22.710 nUM 1,2,3,4,5
View 2 Replies
View Related
Aug 31, 2004
Hi,
I am looking for some help in MS SQL server. I want to access the columns of a table without using the names of the colulmns.
Example - SELECT table1.field[1], table1.field[2] FROM table1;
Any information to this effect is much appreciated.
cheers/- Pradeep
View 9 Replies
View Related
May 8, 2007
I have one column name that is: description
when i write a query the world lights up with blue, I think I saw someone using [ ] around the word but I no longer remember if this is the way to handle reserve words that have been use as columns names
View 10 Replies
View Related
Aug 4, 2015
Is there a way to bulk remove spaces from column names from all tables in a db?
View 6 Replies
View Related
Jun 3, 2015
I have a number of tables, that I need to copy with data intact, about once a year.
Let's say the table name is 'dbo.MyTable'
I just do a SQL Statement like this:
SELECT * INTO dbo.MyTable'BU06032015 FROM dbo.MyTable
How can I automate this so that it does the Select Into statement and automatically adds the 'BU' + the current date?
View 5 Replies
View Related
Aug 14, 2015
How can I bulk remove spaces within table names in the whole db?
View 3 Replies
View Related
Jul 14, 2015
I have a table as below and need getting the desired result as below
Col1 Col2 Col3
A B
C
---desired result
t1 t2
Col1 A
Col2 B
Col3 C
[URL]
View 3 Replies
View Related
Mar 31, 2006
I've called a resultset from SQL Server
using an SQL Selection. I need to iterate over that entire result set
(200+ columns/fields) and all I need are the random numbers contained
in any of the rows/columns. I don't want to have to name each
field/column and then use an if > 0 statement.Isn't there
some way to generically loop through the column's by index or something
instead of their field name so I can just use an integer loop to walk
the dataset? I know there is I've done it about 5 years ago. The
question is how do you do it in C#?SqlConnection thisConn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLQuery"].ConnectionString); SqlCommand thisCmd = new SqlCommand("Command String", thisConn); thisCmd.CommandText = "Select * from SelectionsByCountry where [" + DropDownList1.SelectedItem.ToString() + "] > '0'"; thisConn.Open(); SqlDataReader thisReader = null; thisReader = thisCmd.ExecuteReader(CommandBehavior.CloseConnection); while (thisReader.Read()) { DropDownList2.Items.Add(thisReader["System"].ToString().Trim());/*** There are 200+ columns left I want to walk over using a loop structure of some sort. How do I do that?*/ }- Rex
View 2 Replies
View Related
Nov 12, 2001
I'm looking for a query that will return all index names, the table the index is on and the columns in the index...
View 1 Replies
View Related
