Output Column Names In Each Row Along With The Row Value
Jun 29, 2007
Hello,
I was wondering if anyone can help me figure something out.
Is it possible to do a querey in MS SQL server and have the results
returned so that each result in each row is preceeded by the column
name?
eg. instead of usual output -colName1, colValue1,
colName2,colValue2,colName3,colValue3 ?
Also I would like to only have this for certain columns ie in the
above example only for columns 2 and 3
Thank you! :-)
Yas
View 4 Replies
ADVERTISEMENT
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
View 6 Replies
View Related
Jan 22, 2004
Hi
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.
View 5 Replies
View Related
Dec 8, 2006
I am working on an OLAP modeled database.
I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).
I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.
The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.
What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?
I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.
Thanks in advance for any assistance you can provide.
View 9 Replies
View Related
May 28, 2015
I've been asked to create a new SQL server and restore an old server's DBs to the new server.
I'd like to generate a list of the DB names using powershell to distribute to their creators, in case some of these DBs are no longer needed.
View 2 Replies
View Related
Apr 28, 2008
I need to create the following table in reporting services
PRODUCT April March Feb
2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6
My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???
Thanks in advance!!!
View 1 Replies
View Related
Dec 29, 2003
Hello,
Im trying to get the column names from a database and display them in textboxes. someone has already helped me by tellnig me that i need to use the FillSchema command. Which works just fine and I can see only the colum names in a datagrid when i bind it to that.
The problem is that I do not know how to extract the name of a column and put it in to a textbox ?
does anybody know how I can fo this ?
Thanks a million
Rob
View 3 Replies
View Related
Aug 24, 2005
Hi,How do I display the column names from my Sql server table?In asp3 the recordset allowed thisforeach key in rs ColumnName = key.name ColumnValue = key.valuenextHow do I do this in .Net?I want to use a DataReader so I can read through each record and only display the ones I want.TABLE_ONEColumn_OneColumn_TwoColumn_Threethanks,
View 1 Replies
View Related
Mar 30, 2004
Hi, i need a query to have the columns names !!
If you have another solution, let me know !!
superj !!!
View 12 Replies
View Related
Apr 10, 2008
Hi
When I run this query..
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='TableTest'
I get all the column names, but the result is not in the same order as when I check the design for the table, why is that?
View 2 Replies
View Related
Dec 27, 2005
At my work, we are upgrading a number of MS Access 2000 databases to SQL Server 2000. Many of the columns have names that do not follow the rules for Identifiers. For example there are columns with a numeric names 1,2,3,4,5,6,7,8,9. There are also columns with a hyphen in the name and columns that begin with a number and not an underscore or an alpha character. Plus there are columns with names like first, last, position, etc. (There was also columns with reserved words used as the names. I have changed those column names in the databases that I have already converted) Will leaving these names alone create a problem? The database serves as the backend to ASP pages.
Miranda
View 4 Replies
View Related
Jan 1, 2008
Hello,I'm still extremely novice to SQL and I've tried googling how to produce this result but I've been encountering a lot dynamic sql commands which isn't exactly what I want. If this is an ignorant question I do apologize but heres my scenario: I have a table with columns of the day, for instance: Monday_hasData (bit)Monday_DataAnd what I want to do is essentially pass in "Monday" as a parameter and rather than doing an If statement on each row, I would like to just like to do something like assign @dayCheck + "_hasData" to a variable and then use @dayCheck as part of my query. Is this possible or am I going to have to have 7 cases? Thank you for any input,Chance
View 2 Replies
View Related
May 30, 2008
Hi all,
By using below query i can get no of tables having the give column name in a particular database,
SELECT COUNT(*) AS CounterFROM syscolumnsWHERE (name = 'empno')
but i want to know the table names too?
any one please suggest me how to find table names too......
In other words i know the particular column name and right now i want to know the table names in which this column name exists.
View 2 Replies
View Related
Jul 12, 2002
Hi,
I have a table in which out of 20 columns, there will be data only in a few columns. So, I need to find those column names which have data in them. I was able to do it using the information_schema and using a cursor to loop through all the column names and find out which columns have data in them. But i need to know if there is a more efficient way of doing this without using the cursor. Can somebody please let me know how this can be done without using a cursor? Thank you.
View 2 Replies
View Related
Nov 26, 2002
Can anyone help me with a SQL statement that will list all the column names in a table please ?
I just want to list out the column name so that i can develop asp/vb more effectively than having to use SQLEM and Design table to see the field names.:confused:
thanks
FatherJack
View 2 Replies
View Related
Jun 10, 2004
Hi is possible to create dynamic column name
example
Declare
@StartDate as dateTime
Select @StartDate = '2004-06-05'
select
SUM(Case When table1_date BETWEEN dateadd(day,-6,@StartDate) and @StartDate then 1 else 0 end)AS [dateadd(day,-6,@StartDate)],
SUM(Case When table1_date BETWEEN dateadd(day,-13,@StartDate) and dateadd(day,-7,@StartDate) then 1 else 0 end)AS [dateadd(day,-13,@StartDate)]
from
table1
View 2 Replies
View Related
Aug 19, 2004
I would like to pass into a stored proc the column names I want it to return; how do I do this please?
So I pass in say:
@p_AccountNumber with a value of 'AccountNum'
and the SELECT that the sp fires is of the form
SELECT AccountNum from AccountTable
Any help appreciated
View 2 Replies
View Related
Jan 19, 2005
thanx for the help in advance,
i have a table with Monday, Tuesday, Wednesday. .... Sunday
i get the weekday by
select @stat = datename(dw, getdate()) which in this case is Wednesday
but when i do something like
select top 2 * from myTable
where 'myTable.'+@stat = 1
which is as same as
select top 2 * from myTable
where myTable.wednesday = 1
i receive a syntax error. how can i dynamically select myTable.Wednesday ?
i also tried using
select top 2 * from Intercon
where '@stat' = '1'
but then this doesn't return anything where it should.
any help?
View 2 Replies
View Related
Mar 7, 2005
Is there a way that you could get the column names for each table in a database using 1 query?
something like:
tbl colname
t1 catID
t1 catName
t2 prodID
t2 prodDesc
t3 cartID
...
...
I know it would be long, but I would just be searching through the saved output for specific names.
View 1 Replies
View Related
Nov 15, 2005
Hi All,
I was wondering how would I get the column names from a table? Not the results just a listing of column names. What is the command to get this information? I am using MS Access, but I posted here because MS SQL is the closest thing on this forum and the syntax is usually similar.
Thanks a bunch.
Val
View 5 Replies
View Related
Oct 13, 2005
SQL server throws an exception when I try to run the following code:
ALTER TABLE contacts ADD default ntext NULL;
The reason for that is that "default" is an SQL keyword.
The way I handle this now is I put an underscore at the front:
ALTER TABLE contacts ADD _default ntext NULL
Is there a cleaner way to handle this, i.e. to keep the column name "default" and forse sql server to create a column with this name?
Thank you!
View 9 Replies
View Related
Jun 22, 2007
Morning guys,
I have a project where Iam suppose to script all columns in every database/table/columns on the network.
I figured the best way to do this would be using sql-dmo (Sql Server 2000)
Does anyone have any suggestions on getting started with this project?
thanks,
Jonathan
View 14 Replies
View Related
Nov 20, 2007
Hello, everyone:
I used bcp to generate a txt file as:
EXEC master..xp_cmdshell 'bcp "SELECT * FROM Test..Orders" queryout "c:/test.txt " -U tester -P tester -c'
It works fine except without column names of table. Does any one have idea that can bring out column names from table?
Thanks
ZYT
View 9 Replies
View Related
Jan 25, 2004
I have been looking in the SQL Server Help Files, but I cant seem to find the syntax to change the name of a column name..?
Can it be done? if so, what is the syntax?
View 4 Replies
View Related
Mar 15, 2004
I am writing a stored procedure in which I have a query that selects the Headings of Columns from another table...
I want to then create a loop that will contain a variable with the value of the column heading and then set the column to a value of NULL...
Is there any way to accomplish this???
I thought about placing these values into a temp table...
This is what I have so far...
Declare @QueryX nvarchar(500)
Declare @FieldName varchar(20)
Create Table #UpdateRejDoc
(
Abbreviation varchar(20)
)
Insert into #UpdateRejDoc
Select Abbreviation
From tbl_Titles
left JOIN tbl_TitleRouting on tbl_Titles.Title_ID = tbl_TitleRouting.Title_ID
Where tbl_TitleRouting.Application_ID = @Application_ID
While Exists (Select Abbreviation from #UpdateRejDoc)
Begin
Set @QueryX = 'Update DBLandfillUser.tbl_ObjectApprovals' +
'@AppName + Set @FieldName = null Where object_id =' + Cast(@object_id as VarChar(20))
End
View 1 Replies
View Related
Apr 22, 2008
Hi All,
Heres my problem...
If I create a table T1 with a couple of columns; MyPKCol, ColA
I then create a view V1 as "Select * From T1"
At this stage, if I run "Select * From V1" the result will, as expected, include following columns:
MyPKCol,ColA
If I now add another column, ColB, to T1 and then again run "Select * From V1", I still get the result with only 2 columns;
MyPKCol,ColA
In order for V1 to return MyPKCol,ColA,ColB I have to drop V1 and then recreate it again.
Can someone explain why this is and how I can clear this type of cache?
I've tried the following commands, but it didn't work:
DBCC FREEPROCCACHE;
DBCC FREEsystemCACHE( 'ALL' );
DBCC DROPCLEANBUFFERS;
There has to be an easier way than having to recreate SPs, Function and Views just because you make a change to a table.
I hope I made sence
Thank you in advance for any help.
roamso
View 8 Replies
View Related
Dec 2, 2013
I have a SQL table with 5000 rows in it. Some of the other SQL columns have different values but if I wanted to swap say 500 of the rows would it be something like
UPDATE Coupins
SET Name='test1,test2,test3'
WHERE Name='test4,test5,test6';
I need basically to swap the Name value of these without affecting any other values.
View 4 Replies
View Related
Feb 15, 2006
I'm trying to perform an insert on a table from another table, but
the fieldnames are different. The data is similar, but there are
less fields as well. Does anyone know how to accomplish this? All
the material I've read on the Internet so far pertaining to Insert
statements, has all the column names matching up.
Any help would be greatly appreciated.
View 3 Replies
View Related
Aug 28, 2007
Got my first real job to do in SSIS and it's not really much fun. Here goes. I have excel files from various vendors, none of which are in the same format. Now I'm less concerned with the names, I have those in variables so that's not a big deal. My issue is around the column names. Let's say we have file a, b and c. In file a the first name field is called [first name], in file b it's first_name and in file c it's firstname. What I want to do is create 1 package that can handle these files regardless of how first name is spelled. I was thinking I could open the file and somehow search for a column name that contained the word "first%" and then map the column name to a variable. I'm sort of stuck though on how to actually do that. Help. And please don't tell me to tell the vendors to standardize. That's the end goal but it's like herding cats.
Mike
"oh, that monkey is going to pay"
View 3 Replies
View Related
Jul 23, 2005
I have set up a query where I am using a cursor to pass result from onequery to use as select parameters in another query. The problem I amhaving is that when I pass the results into the second query I get thecolumn headers?How do you suppress the column headers from showing in the query? Isee how you select options - print headers.ThanksJason
View 2 Replies
View Related
Jul 23, 2005
Hi,I am learning Ms Sql and I found that a lot of the tables I am finding( in different tutorials ) are using special names for their columnnames such like au_username, au_salery ...Is the any naming convention for column names, or just at all is thereany reason for naming the table columns in specific way.Thanks in advance.
View 4 Replies
View Related
Jul 23, 2005
I am struggling on this issue and was hoping if anyone out there canhelp me.Here is the setup: I have a table with the following data:TableName: TranDetailMerchID ItemName Price------------------101 A 5101 B 3.5101 C 0102 B 7.6102 C 4102 E 65102 G 4103 K 35Table DesignMerchID intItemName varchar(50)Price floatWhat I would like is a report that looks like thisMerchID A B C E G K101 5 3.5 0102 7.6 4 65 4103 35This report can change on every run depending on data in tableTranDetail. The column name in report depends on ItemName in tableTranDetail. As seen in the above report, there is no data for Items D,F, H, I, J and hence they do not show up in the report.What I need: Code for a stored procedure that can get me this data.Thanks for your help...DBA in despair!
View 7 Replies
View Related
Aug 15, 2005
I'm dealing with a database with tables that have freaking columns.Partial DDL:Create table [tbl IMN] ([Ave Cost-Mn] varchar(10))-- yeah, this column contains string valueNow, I'd like to rename all these freaking columns without specialcharaters like '-', 'whitespace' etc systematically (meaning loopthrough all tables and columns dynamically). It seems that thesp_rename proc can't handle some function call or ...?e.g.exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[AveCost-Mn]','-',''),'COLUMN'better if it can work,exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave Cost-Mn]','-|',''),'COLUMN'TIA
View 6 Replies
View Related
