Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 PDW string conca fail

Author  Topic 

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-02-19 : 03:26:39
not working query

CREATE TABLE a(Col1 varchar(20), Col2 int, Col3 int)WITH(CLUSTERED COLUMNSTORE INDEX )
INSERT INTO a SELECT 'Apple', 1, 2

DECLARE @SQL VARCHAR(8000)

SET @SQL = 'SELECT a.Col1, upvt.ColumnName, upvt.ColumnValue FROM a CROSS APPLY('
+ STUFF((SELECT ' UNION ALL SELECT ''' + CAST(COLUMN_NAME AS VARCHAR(125)) + ''' AS ColumnName, ' +
CAST(COLUMN_NAME AS VARCHAR(125)) + ' ColumnValue' FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'a' AND ORDINAL_POSITION <> 1 FOR XML PATH('')), 1, 11, '')
+ ')upvt'
EXEC (@SQL)



tried also
SELECT @SQL = @SQL + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'a'

tried also
update #a set column_list = columnlist + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'a'

any idea?

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-02-19 : 03:28:58
not sure this include at this post
select @@version

Microsoft SQL Server 2012 - 10.0.4429.0 (X64) Sep 13 2013 12:06:46 Copyright (c) Microsoft Corporation Parallel Data Warehouse (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-19 : 09:29:31
There are a LOT of things not working in PDW.
The language of PDW is actually D-SQL, not T-SQL.

See http://download.microsoft.com/download/4/2/6/42616D71-3488-46E2-89F0-E516C10F6576/SQL_Server_to_SQL_Server_PDW_Migration_Guide.pdf




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-02-19 : 19:45:27
yea, i notice that too....might as well using loop to get this work.
Go to Top of Page
   

- Advertisement -