Transact SQL :: Vertical Column Names With Data?
Jul 14, 2015I 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]
ADVERTISEMENT
Transact SQL :: Find Table And Column Names From String Data
May 29, 2015I have a SQL text column from SP_who2 in table #SqlStatement:
like 1row shown below :
"update Panel set PanelValue=7286 where PanelFirmwareID=4 and PanelSettingID=9004000"
I want to find what table and column names are in the text ..
I tried like below ..
Select B.Statement from #sp_who2 A
LEFT JOIN #SqlStatement B ON A.spid = B.spid
where B.Statement IN (
SELECT T.name, C.name FROM sys.tables T
JOIN sys.columns C
ON T.object_id=C.object_id
WHERE T.type='U'
)
Something like this : find the column names and tables name
Transact SQL :: Get Table And Column Names From XML
Jul 6, 2015I 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'
Transact SQL :: Getting Table And Column Names
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
Transact SQL :: Bulk Column Names Rename
Aug 4, 2015Is there a way to bulk remove spaces from column names from all tables in a db?
View 6 Replies View RelatedTransact SQL :: Renaming Pivot Table Column Names Dynamically
Oct 30, 2015I got a table which gets populated by stor proc where we pivot the Sum(Balance of mortgage) by YYYYMM for the whole duration of the loan term.
I have a requirement to rename the column header where the previous month end balance period be renamed to P0.
if we run the report today, then the balance as at 31/09 should show under column P0 which now shows under 201509 and then P0 keeps shifting with each month run.
How do I dynamically rename the column headers.
Transact SQL :: Creating Dummy Headers (column Names) With Union All
May 19, 2015I want to create a raw SQL resultset for outputting to Excel with some artificial headers transposed over the top of the 2nd part of the Union's column names. The first part of the Union will be the Headers. Like this, the space to the left of the topmost columns is preferably empty ....
COL 1 COL2 COL3 COL4 etc. BEH BIG BPL etc.
************************************* INTAKT DEFEKT INTAKT DEFEKT INTAKT
************************************* B E B E B
I just want the text above as a 3 line header and there won't be any values obviously. Then the 2nd query will be joined to the above with a Union all. The 2nd query has all the same column names as what will be given in the first set above. What is the SQL Syntax for doing so? Do I have to use a from clause?
Modifying Data Column Names And Data Types
Mar 13, 2008I'm in the process of converting a rather huge VSAM database into a set of SQL tables.
I am using the same data names from the mainframe (like XDB-NAME to RDB-NAME).
I load the files using Import Export Data and it makes the tables with such column names as col001, col002, col003, etc... and always sets the data types to varchr(255).
And I have to cut and paste the data names from the manframe side to the server side (and the data types to.)
So, is there an easier way to do this? Or am I doomed to cut-n-paste my days away...
Thanks for any help.
Bcp Data Extractions With The Column Names!
Jul 18, 2007Ok, i am finally giving in on this one and asking for some help! I am trying to set up a T-SQL Statement that will extract data from all the tables in the current database to a csv file including Column names!
I know that bcp can not handle the column names, so i tried to get around this with an append of the column names from a select, but unfortunatly the select gives me the names in Alpha order and not the order of the fields.
I have tried putting in an order by on the select, but this does not seem to have any effect. I have included the snippet of my script that is causing the problem here :
-- set up the echo command
select @colcommand= 'exec master..xp_cmdshell'
+ ' '''
+ 'echo ' + @names + ' >> c:cp' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + '.txt'
+ ''''
from INFORMATION_SCHEMA.TABLES where TABLE_NAME=@TABLE
and just in case you are interested in the rest of the script, the full monster is included at the bottom of the post. Also if you can see any more efficient ways of doing what i am trying to do, please let me know!
Thank you for your help in advance.
Mark
---------------------------------------------------------
-- Script to create a csv file of data from all tables inside current database
-- declare all variables
declare @command varchar(200) -- command used for bcp
declare @fetch_status int-- variable for fetch status in cursor
declare @TABLE varchar (200)-- Variable to hold table name
declare @colcommand varchar (200)-- Variable to hold column creation command
declare @count int-- Variable used to determine first itteration of Column loop
declare @names varchar(100) -- variable used for the column names
declare @delimiter varchar(10)-- variable used for delimiter in column names
SET @delimiter = ','-- set up the delimiter to comma
select @count=0-- initialises the COUNT variable
-- setup cursor to create the bcp command to backup the data files to csv format
declare bcpcommand cursor READ_ONLY FOR
select 'exec master..xp_cmdshell'
+ ' '''
+ 'bcp'
+ ' '
+ TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME
+ ' out'
+ ' c:cp'
+ TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME
+ '.txt' + ' -c -t,' + ' -T'
+ ' -S' + @@servername
+ ''''from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'
-- setup cursor to pick up all the tables in the given database (used for column names section)
declare dbtables cursor READ_ONLY FOR
select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'
open bcpcommand
select @fetch_status=0
while @fetch_status=0
begin
fetch next from bcpcommand into @COMMAND
select @fetch_status=@@fetch_status
if @fetch_status<>0
begin
continue
end
-- print 'Command to be run : ' + @COMMAND
EXEC (@COMMAND)
end
-- close and tidy up
close runme
deallocate runme
-- now create the fieldname files and then echo the 2 files together!
open dbtables
select @fetch_status=0
while @fetch_status=0
begin
fetch next from dbtables into @TABLE
select @fetch_status=@@fetch_status
if @fetch_status<>0
begin
continue
end
SELECT @names = COALESCE(@names + @delimiter, '') + name
FROM syscolumns where id = (select id from sysobjects where
name=@TABLE)
-- due to the concatonation used, the second itteration onwards has a , attached to the front of the line
-- this section removes the first char
if @count <> 0
begin
Select @names=SUBSTRING(@names,2,198)
end
-- set up the echo command
select @colcommand= 'exec master..xp_cmdshell'
+ ' '''
+ 'echo ' + @names + ' >> c:cp' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + '.txt'
+ ''''
from INFORMATION_SCHEMA.TABLES where TABLE_NAME=@TABLE
-- print 'COMMAND : ' + @colcommand
exec (@colcommand)
-- reset @names variable for next itteration, and set count to 1 to trigger IF above
select @names=''
select @count=1
end
-- close and tidy up
close dbtables
deallocate dbtables
Column Names In Data Warehouse
Mar 12, 2008Hi all,
I'm working on my first data warehouse and I'm not sure how I should name the columns in the database.
The first phase of the data warehouse is to store a bunch of data from one third party source. The source contains over 100 pieces of data and the business user doesn't even know what some of the fields are but he wants to store everything. The third party refers to the each field with a somewhat cryptic short name and a longer description. The short name isn't always cryptic.
My question is am I better off naming my columns the same as the source system's short name so that I can easily debug problems later? Should I instead try to shorten their definition into something meaningful? On a side note, I'm 100% positive that we'll never populate the tables in questions with data from an additional source.
Thanks!
Replicating Data To Tables Of Different Column Names
Apr 17, 2007hi,
is it possible to replicate data from one table to another which have different names,different schemas and different column names.
Im trying this using transactional replication.
i achieved this for different schema, different table names.but it is failing for different column names.
till now i haven't got a proper reply for my problem.
regards
Baji Prasad.B
Data Access :: How To Find Column Names In A Table
Sep 11, 2015how to find the columns names in a table in sql 2008r2.
as i need to compare the midsing fields in the table from two database.
Reporting Services :: Obtaining Column Names From Data?
May 16, 2015Got a table like:
Reporting_DateCustomerSales...
In such a case need a view with custom headers with a prefix of Latest date found in the first field of Reporting_Date.
In other words if Reporting_Date consists data like following six entries:
1-JAN-201031-DEC-2013NULL14-AUG-2014NULL31-MAR-2015
Then, could there be anyway to achieve a View with the headers (i.e. complete data without any filtration) in the following form:
Mar312015_Reporting_DateMar312015_CustomerMar312015_Sales...
Determine Table Names And Column Names At Runtime?
Jan 22, 2004Hi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Thanks.
How To Append Data To A Destination Flat File Without Appending The Column Names Again
Mar 13, 2008Hello guys,
here is my issue.
I created a ssis package which exports the data from oledb source to flat file (csv format). For this i have OLEDB source and Flat File as destination. I generate the file and filename dynamically with the column names in the first row. So if the dynamically generated file name already exists , then i want to append the data in the same existing file. But I dont want to append the column names again. I just want to append the rows to the existing rows.
so lets say first time i generate a file called File1_3132008.csv.
Col1, Col2
1,2
3,4
After some days if my ssis package generates the same file name i.e. File1_3132008.csv, this time i just want to append the rows to the existing file. So the file should look like this-
Col1, Col21,23,45,67,8
But instead my file looks like this if i set Overwrite propery to false
Col1,Col2
1,2
3,4
Col1,Col2
5,6
7,8
Can anyone help me to get the file as shown in the highlighed
Any help would be appreciated .
Thanks
Transact SQL :: XML Column Data Into Separate Column
Nov 18, 2015I have a column with the data as below :-
<Items>
<Item Value="Value1" />
<Item Value="Value2" />
<Items>
How to get this data into seperate columns as
Items
value1
value2
Subreport With 3 Columns Renders In Main Report As A Single Vertical Column.
Aug 28, 2007I have embedded a subreport in the detail section of my report. When I view the subreport in page preview mode it renders with three columns, but when it is rendered w/in the main report in page preview mode the 3 column layout is rendered as a single verticle column.
I've defined the columns in the Body/Columns. I have also set the report inteactivesize and pagesize heights in an attempt to remedy the issue. Margins are set to 0.
Any help is appreciated.
Excel Destination Data Flow Component Shows No Sheet Name Or Output Column Names For Mappings
Mar 8, 2008
I have a data flow that consists of
OLE DB source which calls a stored proc that returns a result set
data conversion
Excel destination
I am in design mode in Business Intelligence studio. My excel destination (with an Excel Connection) shows no sheet name though I have an execute SQL task before the data flow to create the excel table called SHEET1. Needless to say, there are no output columns visible to do any mappings. I did go to the ExcelConnection to set the OpenRowset Property to SHEET1 but it seems to have no effect.
I can do the export in SQL Server Management studio and that works fine, but it is basic and does not meet my requirements. I have to customize the package to allow dynamic Excel filenames based on account names and have to split my result set into multiple excel sheets because excel 2003 has a max of 65536 rows per sheet. Also when I use the export wizard, I have the source as a table and eventually the source has to be a stored proc with input parms.
What am I missing or doing wrong? Thanks in advance
Transact SQL :: Key And Indexes On Two Column Data Table Or Parsed View (Large String Of Data And Filename)
Oct 4, 2015I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A:
(name phone house cost of house,zipcodecountystatecountry)
-a view will later split this large varchar string based
column b: is the source filename of the data load (varchar 256)
....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
Transact SQL :: Table Row Data Show In Column Data
Oct 22, 2015I have a simple table data i want want to show row data in to column data.
SELECT clblcode,mlblmsg
FROM warninglabels
My expected result will be
0001 0002 0003 0004
------- ------- -------- --------
May Cause.... Important..... Take madi........... Do Not Take.......
Could Anybody Tell Me How To Convert Vertical Data Into Horizontal Data?
Sep 2, 2005Could anybody tell me how to convert vertical data into horizontal data?I have a one-to-many relationship in sql server 2KProduct, ProductAccessory, one Product has many ProductAccessories.My Table design is like this:Table Product{ ProdId int, ProdNameId int, ....}Table ProductAccessory{ ProdId int, AccNameId int, AccUnitId int, ....}Because one Production has at most 4 ProductAccessoryI want to use a SELECT statement OR function to return ProdId, ProdNameId, AccNameId1, AccUnitId1, AccNameId2, AccUnitId2, AccNameId3, AccUnitId3, ....Any help will be appreciated! Thanks a lotJoseph
View 3 Replies View RelatedConverting Vertical Data To Horizontal
May 25, 2006I have a table as follows
opendate (datetime) callnumber (int) closed (bit)
I want to find how many calls were opened today and of those how many are closed
I have come up with the code below but again am looking for
1. a more elegant solution
2. a way to generalise this to show the same information for x number of days
create table #holdit1
(opencount int)
create table #holdit2
(closedcount int)
insert into #holdit1
SELECT count(*) as opencount
FROM [dbo].[problog]
WHERE datediff(dd, opendate, getdate()) = 0
AND closed = 0
group by closed
insert into #holdit2
SELECT count(*) as closedcount
FROM [dbo].[problog]
WHERE datediff(dd, opendate, getdate()) = 0
AND closed = 1
group by closed
select #holdit1.opencount AS CallsOpen, #holdit2.closedcount AS CallsClosed, #holdit1.opencount + #holdit2.closedcount AS AllCalls
from #holdit1 cross join #holdit2 #holdit2
DROP TABLE #holdit1
DROP TABLE #holdit2
this gives me
CallsOpen CallsClosed AllCalls
----------- ----------- -----------
1 3 4
Select Data By Vertical Dimension
Mar 12, 2007Dear everyone,
i have a table like below:
id title
1 a
2 b
3 c
and i want to get data from the table above with this format:
column1 column2 column3 column4
1 a 2 b
3 c
How can i do this with select statement or inner join?? or an posible way , please help me
Thnks
Select Data By Vertical Dimension
Mar 12, 2007Dear everyone,
i have a table like below:
id title
1 a
2 b
3 c
and i want to get data from the table above with this format:
column1 column2 column3 column4
1 a 2 b
3 c
How can i do this with select statement or inner join?? or an posible way , please help me
Thnks
When To Make A Vertical Partition In Data Warehous
Jun 5, 2008Hi All,
I am trying to understand, when would I do a vertical partition in a Dimensional Data Warehouse ? What are the things I need to consider, before I take the decision?
Necessity is the mother of all inventions!
How To Convert Data Horizontal To Vertical In Server
Jul 24, 2015Month Discount
Expenses GST
<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="d14582be-eecd-4def-be7e-0cfe00b13c80" id="4279c12f-e03f-4b38-9fc8-eb1a991c25ac"><gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="7f0da910-3775-4ee4-821e-1a0e7ee2ce0b"
id="92ce074f-ee0b-4794-839b-ee50c88d380c">ServiceCharge</gs></gs>
[Code] ....
Transact SQL :: Split Column Data To Row
May 19, 2015I found string from net how to split column data to row
SELECT A.JbIDFull, A.ProdID, A.OrderQty, A.OtherDetails, A.OrderDate, c.Item FROM
(SELECT JbIDFull, DeptID, ProdID, OrderQty, OtherDetails, OrderDate, LamORSteachType, JBID, RMIDs, RMQty
FROM tblJobCardforProduction WHERE JBID = '2' AND DeptID = '3') A
CROSS APPLY dbo.SplitStringNEW(a.RMIDs,';') b
CROSS APPLY dbo.SplitStringNEW(a.RMQty,'/') c
When I apply one cross apply it's working fine but when i apply for one more column is replacing three time one row, this data i want to split RMIDs and RMQty with same JbIDFull
JbIDFull DeptID ProdID OrderQty RMIDs RMQty
PD-May15-00001 3 2044 10000 PROD-00052 0
PD-May15-00002 3 921 1000 PROD-00052;PROD-00383;PROD-00384 500;600;700
Transact SQL :: Put One Column Data Into Many Columns?
Oct 15, 2015how to split a single column into multiple columns with | delimited. For example I have column old_values in this I have a data with | delimited and I need to split this data into multiple columns.
Old_values
xxx|yyy|zzz|aaa|bbb|ccc|ddd
into
A B C D E F G
xxx yyy zzz aaa bbb ccc ddd
Data Access :: Insert Rows To MS Access 2013 Without Listing Column Names
Nov 12, 2015We need to insert data/rows from a SQL Server 2014 database into MS Access database. The problem is, there are so many columns (100+) in the table and there are so many insert transactions of this kind (from different tables) that it is not very easy to write the code in VB.NET that lists all column names.
Both the Access and SQL Server tables have the same number of columns and the equivalent data types, so inserting is not really the problem. It's just that is there a way to do an insert statement in T-SQL that does not name all the columns?
Vertical Partition Of Table With TEXT Data Type
Feb 11, 2008
Hi
Column with TEXT datatype is not stored in the same data row any way. I am wondering if there is any performance gain to put it in a seperate table. Thanks
Transact SQL :: Display Different Table Having Same Column With Different Data
Sep 25, 2015I am having two table i.e( tbl_oldEmployee , tbl_NewEmployee ),which is having Column name Employee Name and City same in both table but inside column data is different in different table.but i want to view the Employee name and City from tbl_NewEmployee to tbl_oldEmployee which is having EmployeeId common with tbl_oldEmployee extra record also required (i.e tbl_NewEmployee having 6 record and tbl_oldEmployee having 10 record,so i need to display data from tbl_oldEmployee but first 6 record which id match with tbl_NewEmployee id should be replaced and extra data from tbl_oldEmployee also display).
View 3 Replies View RelatedTransact SQL :: Any Way To Encrypt Varbinary Column Data?
Nov 4, 2015Is there a way to encrypt 'varbinary' column data?
View 9 Replies View RelatedTransact SQL :: Convert Varchar (max) Column Data
May 20, 2015I have a column in table which stores the actual signature from the singature pad. In the table it is stored as varchar(max) datatype and sample data looks like below. Below data is the actual signature of the person.
CREATE
TABLE #test
([Signature]
VARCHAR(MAX))
[code]...
How do i decrypt this data so i can print actual signature on my report.