PDW string conca fail - SQL Server Forums

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: )