SQLServerCentral.com / T-SQL (SS2K8) / SQL Server 2008 / Put first line as column name / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 08:49:44 GMT20RE: Put first line as column namehttp://www.sqlservercentral.com/Forums/Topic1469672-392-1.aspxThanks to Erin for pointing out that you had posted ddl and sample data in your first post. :blush:Now your most recent post does not match that new sample data that you mentioned. Please help me understand what you are trying to do here.I will go back to my original comment of why are you making everything so overly generic? You have violated so many normalization rules in your ddl I don't know where to begin. You have mixed datatypes in a single column, you have a different number of columns per row. Data integrity, validation, etc etc is impossible here. You can't do any kind of calculations or date conditions because of all the mixed data.I will help you figure out the short term fix but honestly you need a long term solution. Mon, 08 Jul 2013 14:13:06 GMTSean LangeRE: Put first line as column namehttp://www.sqlservercentral.com/Forums/Topic1469672-392-1.aspx[quote][b]ulisseslourenco (7/8/2013)[/b][hr]Hi guys,Could somebody help me? This is a bit urgentBest regards[/quote]In order to help we will need a few things:1. Sample DDL in the form of CREATE TABLE statements2. Sample data in the form of INSERT INTO statements3. Expected results based on the sample dataPlease take a few minutes and read the first article in my signature for best practices when posting questions.Mon, 08 Jul 2013 09:13:00 GMTSean LangeRE: Put first line as column namehttp://www.sqlservercentral.com/Forums/Topic1469672-392-1.aspxHi guys,Could somebody help me? This is a bit urgentBest regardsMon, 08 Jul 2013 07:13:05 GMTulisseslourencoRE: Put first line as column namehttp://www.sqlservercentral.com/Forums/Topic1469672-392-1.aspxYes, you are right about dynamic queries into functions. When I wrote about "the other functions work as well" I said the other functions are inserting information into a table correctly , but I have a function that joins two tables to show a result for example: COL1 | COL2 |COL3 | COL4 | CAR MODEL| Year | Purchase data | color |VW Bettle | 2009 | 2009-01-01 | red |Ford Ka | 2013 | 2013 -05-16 | silver |Based on the function [code="sql"]CREATE FUNCTION DBO.UF_DADOS (@CODIGO VARCHAR(50))RETURNS TABLEASRETURNSELECT COL1_H, COL2_H, COL3_H, COL4_H, COL5_H, COL6_H, COL7_H, COL8_H, COL9_H, COL10_H FROM TB_HEADER WHERE CODIGO = @CODIGOUNION ALLSELECT COL1_D, COL2_D, COL3_D, COL4_D, COL5_D, COL6_D, COL7_D, COL8_D, COL9_D, COL10_DFROM TB_DADOS INNER JOIN TB_HEADER ON TB_HEADER.ID_HEADER = TB_DADOS.ID_HEADER WHERE CODIGO = @CODIGO[/code]I should have a result like a this: CAR MODEL| Year | Purchase data | color |VW Bettle | 2009 | 2009-01-01 | red |Ford Ka | 2013 | 2013 -05-16 | silver |Replacing Col1, Col2, Col3 and Col4 for CAR MODEL,Year,Purchase,data and color. Actually I haven't gotten yet.Best RegardsTue, 02 Jul 2013 16:02:21 GMTulisseslourencoRE: Put first line as column namehttp://www.sqlservercentral.com/Forums/Topic1469672-392-1.aspx[quote][b]ulisseslourenco (7/2/2013)[/b][hr][quote][b]Sean Lange (7/2/2013)[/b][hr][quote][b]ulisseslourenco (7/2/2013)[/b][hr]Thanks for respond me.I have a question: Is allowed use exec statement into a function? I ask that because I have to return a select into a application calling oneSELECT * FROM DBO.UF_DADOS ('CARRO_01')[/quote]No in SQL a function cannot use any dynamic sql. I have to ask, why have you created such a generic piece of code? This is like "one function to rule them all". You would never consider doing this in a programming language yet we see it so frequently in the database world. The performance of things like this degrade as more and more data enters the system and there is nothing you can do to make it faster because everything is so generic.[/quote]The other functions work as well, the problem occurs because the programmer can insert a several of information into that tables and union statement into function could replace header table lines in a column name in function return. As I wrote in initial post I thought use sp_rename, however I afraid decreasing performance.In relation, use other language, I don't know if it's possible, because this process will work into an application that runs sql only.best regards[/quote]Not really sure what you mean by the other functions work as well. You cannot execute dynamic sql in a function in sql server.I think you missed my point about making this so generic. In a programming language you would not create a method that does everything. It seems that is what you are trying to do here. You are creating a procedure/function that can do anything with any data of any type.Tue, 02 Jul 2013 15:26:48 GMTSean LangeRE: Put first line as column namehttp://www.sqlservercentral.com/Forums/Topic1469672-392-1.aspx[quote][b]Sean Lange (7/2/2013)[/b][hr][quote][b]ulisseslourenco (7/2/2013)[/b][hr]Thanks for respond me.I have a question: Is allowed use exec statement into a function? I ask that because I have to return a select into a application calling oneSELECT * FROM DBO.UF_DADOS ('CARRO_01')[/quote]No in SQL a function cannot use any dynamic sql. I have to ask, why have you created such a generic piece of code? This is like "one function to rule them all". You would never consider doing this in a programming language yet we see it so frequently in the database world. The performance of things like this degrade as more and more data enters the system and there is nothing you can do to make it faster because everything is so generic.[/quote]The other functions work as well, the problem occurs because the programmer can insert a several of information into that tables and union statement into function could replace header table lines in a column name in function return. As I wrote in initial post I thought use sp_rename, however I afraid decreasing performance.In relation, use other language, I don't know if it's possible, because this process will work into an application that runs sql only.best regardsTue, 02 Jul 2013 14:55:24 GMTulisseslourencoRE: Put first line as column namehttp://www.sqlservercentral.com/Forums/Topic1469672-392-1.aspx[quote][b]ulisseslourenco (7/2/2013)[/b][hr]Thanks for respond me.I have a question: Is allowed use exec statement into a function? I ask that because I have to return a select into a application calling oneSELECT * FROM DBO.UF_DADOS ('CARRO_01')[/quote]No in SQL a function cannot use any dynamic sql. I have to ask, why have you created such a generic piece of code? This is like "one function to rule them all". You would never consider doing this in a programming language yet we see it so frequently in the database world. The performance of things like this degrade as more and more data enters the system and there is nothing you can do to make it faster because everything is so generic.Tue, 02 Jul 2013 14:09:41 GMTSean LangeRE: Put first line as column namehttp://www.sqlservercentral.com/Forums/Topic1469672-392-1.aspxThanks for respond me.I have a question: Is allowed use exec statement into a function? I ask that because I have to return a select into a application calling oneSELECT * FROM DBO.UF_DADOS ('CARRO_01')Tue, 02 Jul 2013 13:35:46 GMTulisseslourencoRE: Put first line as column namehttp://www.sqlservercentral.com/Forums/Topic1469672-392-1.aspxSomething like this might get you started:[code="SQL"]declare @strSQL as varchar(max)set @strsql = (select 'select COL1_D as ' + col1_h + ', COL2_D as ' + col2_h + ', COL3_D as ' + col3_h + ', COL4_D as ' + col4_h + ', COL5_D as ' + col5_h + ', COL6_D as ' + col6_h + ', COL7_D as ' + col7_h + ', COL8_D as ' + col8_h + ', COL9_D as ' + col9_h + ', COL10_D as ' + col10_h + ' from tb_dados ' + 'inner join tb_header on tb_dados.ID_header = tb_header.ID_Header ' + 'where tb_header.codigo = ''CARRO_01''' from tb_header where codigo = 'CARRO_01' )exec (@strSQL)[/code]Tue, 02 Jul 2013 12:07:12 GMTErin RamsayPut first line as column namehttp://www.sqlservercentral.com/Forums/Topic1469672-392-1.aspxHi people,I am creating a function to be used in a specific application that a user can insert any kind of information into a table. This code below:[code="sql"]IF NOT EXISTS(SELECT * FROM SYS.tables WHERE name = 'TB_HEADER')CREATE TABLE TB_HEADER(ID_HEADER INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, CODIGO VARCHAR(50) UNIQUE, COL1_H VARCHAR(100), COL2_H VARCHAR(100), COL3_H VARCHAR(100), COL4_H VARCHAR(100), COL5_H VARCHAR(100), COL6_H VARCHAR(100), COL7_H VARCHAR(100), COL8_H VARCHAR(100), COL9_H VARCHAR(100), COL10_H VARCHAR(100))GO IF NOT EXISTS(SELECT * FROM SYS.tables WHERE name = 'TB_DADOS')CREATE TABLE TB_DADOS(ID_DADOS INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, ID_HEADER INT, COL1_D VARCHAR(100), COL2_D VARCHAR(100), COL3_D VARCHAR(100), COL4_D VARCHAR(100), COL5_D VARCHAR(100), COL6_D VARCHAR(100), COL7_D VARCHAR(100), COL8_D VARCHAR(100), COL9_D VARCHAR(100), COL10_D VARCHAR(100))GOINSERT INTO TB_HEADERSELECT CODIGO = 'CARRO_01', COL1_H = 'MODELO_CARRO', COL2_H = 'ANO', COL3_H = 'DATA_COMPRA', COL4_H = 'COR', COL5_H = 'PLACA', COL6_H = 'COL6', COL7_H = 'COL7', COL8_H = 'COL8', COL9_H = 'COL9', COL10_H = 'COL10'GO INSERT INTO TB_HEADERSELECT CODIGO = 'IMOVEL_01', COL1_H = 'TIPO_DE_IMOVEL', COL2_H = 'LOCALIZAÇÃO', COL3_H = 'VALOR_ENTRADA', COL4_H = 'QTD_PARCELA', COL5_H = 'VALOR_PARCELA', COL6_H = 'MOEDA', COL7_H = 'COL7', COL8_H = 'COL8', COL9_H = 'COL9', COL10_H = 'COL10'GO INSERT INTO TB_DADOSSELECT ID_HEADER = ID_HEADER, COL1_D = 'PALIO', COL2_D = '2012', COL3_D = '05/02/2013', COL4_D = 'VERMELHO', COL5_D = 'XPT 1515', COL6_D = 'COL6', COL7_D = 'COL7', COL8_D = 'COL8', COL9_D = 'COL9', COL10_D = 'COL10'FROM TB_HEADER WHERE CODIGO = 'CARRO_01'GO INSERT INTO TB_DADOSSELECT ID_HEADER = ID_HEADER, COL1_D = 'CRV', COL2_D = '2011', COL3_D = '23/06/2012', COL4_D = 'PRATA', COL5_D = 'ABC 2020', COL6_D = '', COL7_D = '', COL8_D = '', COL9_D = '', COL10_D = ''FROM TB_HEADER WHERE CODIGO = 'CARRO_01'GO INSERT INTO TB_DADOSSELECT ID_HEADER = ID_HEADER, COL1_D = 'APARTAMENTO', COL2_D = 'BARRA DA TIJUCA', COL3_D = '200.000,00', COL4_D = '48', COL5_D = '8.000,00', COL6_D = 'R$', COL7_D = '', COL8_D = '', COL9_D = '', COL10_D = ''FROM TB_HEADER WHERE CODIGO = 'IMOVEL_01'GO CREATE FUNCTION DBO.UF_DADOS (@CODIGO VARCHAR(50))RETURNS TABLEASRETURNSELECT COL1_H, COL2_H, COL3_H, COL4_H, COL5_H, COL6_H, COL7_H, COL8_H, COL9_H, COL10_H FROM TB_HEADER WHERE CODIGO = @CODIGOUNION ALLSELECT COL1_D, COL2_D, COL3_D, COL4_D, COL5_D, COL6_D, COL7_D, COL8_D, COL9_D, COL10_DFROM TB_DADOS INNER JOIN TB_HEADER ON TB_HEADER.ID_HEADER = TB_DADOS.ID_HEADER WHERE CODIGO = @CODIGOGO[/code]Although big query, that is a query function creation. The problem is: How can I return first lines from "tb_Header" to be column name?, I thought in "sp_rename", but I afraid it can be slow in a big table. I'd like to emphasize that columns are always dynamic. Part of code below[code="sql"]CREATE FUNCTION DBO.UF_DADOS (@CODIGO VARCHAR(50))RETURNS TABLEASRETURNSELECT COL1_H, COL2_H, COL3_H, COL4_H, COL5_H, COL6_H, COL7_H, COL8_H, COL9_H, COL10_H FROM TB_HEADER WHERE CODIGO = @CODIGOUNION ALLSELECT COL1_D, COL2_D, COL3_D, COL4_D, COL5_D, COL6_D, COL7_D, COL8_D, COL9_D, COL10_DFROM TB_DADOS INNER JOIN TB_HEADER ON TB_HEADER.ID_HEADER = TB_DADOS.ID_HEADER WHERE CODIGO = @CODIGOGO[/code]grateful for your attentionTue, 02 Jul 2013 11:03:09 GMTulisseslourenco