0

I need to select all column but without 2 column as shown in below

CREATE TABLE product(id int, name varchar(50), inf varchar(50));
CREATE TABLE users(id int,prdct_id int,user1 float,user2 float, ...... , usern float);

select p.id,p.name,p.inf,u.* ???("without id and prdct_id column)???    
from product p join
     users u
     on p.id = u.prdct_id

I wish,u guys understand to me, how can I select all users column without id and prdct_id columns. I don't want to write each of users name to select.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
caglar SARIKAYA
  • 1
  • 1
    Which `RDBMS` you are using – Pரதீப் Aug 27 '16 at 08:38
  • This requires `Dynamic Sql` which varies from `RDBMS` to `RDBMS` – Pரதீப் Aug 27 '16 at 08:40
  • I guess that the total number of `useri` columns is unknown, otherwise there is no point to the question. Under this assumption the only way (I can see) is using dynamic SQL (meaning programmatically construct your query as a string and then request the DBMS to execute it). Different DBMSs have different ways to support that. – FDavidov Aug 27 '16 at 08:41
  • 1
    Possible duplicate of http://stackoverflow.com/questions/413819/select-except and http://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea – JonyD Aug 27 '16 at 08:50

2 Answers2

0

Use the below script for the desired result..

DECLARE @selectlist NVARCHAR(max)
DECLARE @QueryString NVARCHAR(max)

SELECT @selectlist='p.id,p.name,p.inf,'+stuff((SELECT distinct ',u.' + ISNULL([name],'')
FROM  syscolumns 
WHERE id= OBJECT_ID('users')
AND name not in ('id','prdct_id')  FOR XML PATH('')),1,1,'')

SET @QueryString='SELECT '+@selectlist+' FROM product p 
                    JOIN users u ON p.id = u.prdct_id'

EXEC (@QueryString)
Unnikrishnan R
  • 4,965
  • 1
  • 12
  • 21
0
DECLARE @QueryStr VARCHAR(500),@EXECStr VARCHAR(200)

SELECT @QueryStr = 
(
    STUFF( ( SELECT ',' + name FROM  syscolumns 
    WHERE name NOT IN ('Column_name') AND id= OBJECT_ID('table_name') FOR XML PATH('') ),1,1,'' )
)

SET @EXECStr = 'SELECT '+@QueryStr+' FROM table_name'

EXEC (@EXECStr)
Gergely Toth
  • 6,638
  • 2
  • 38
  • 40
Mansoor
  • 4,061
  • 1
  • 17
  • 27