Pivot Table / CUBE ?

Please see the attached fileThere are two options.
If you know the column name than use the first option (see sample) if you do not know it than use the second option (it is dynamic execution).

Eyal

--Second option
CREATE PROC sp_CrossTab
@.table AS sysname, -- Table to crosstab
@.onrows AS nvarchar(128), -- Grouping key values (on rows)
@.onrowsalias AS sysname = NULL, -- Alias for grouping column
@.oncols AS nvarchar(128), -- Destination columns (on columns)
@.sumcol AS sysname = NULL -- Data cells
AS

DECLARE
@.sql AS varchar(8000),
@.NEWLINE AS char(1)

SET @.NEWLINE = CHAR(10)

-- step 1: beginning of SQL string
SET @.sql =
'SELECT' + @.NEWLINE +
' ' + @.onrows +
CASE
WHEN @.onrowsalias IS NOT NULL THEN ' AS ' + @.onrowsalias
ELSE ''
END

CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @.keyssql AS varchar(1000)
SET @.keyssql =
'INSERT INTO #keys ' +
'SELECT DISTINCT CAST(' + @.oncols + ' AS nvarchar(100)) ' +
'FROM ' + @.table

EXEC (@.keyssql)

-- 6
DECLARE @.key AS nvarchar(100)
SELECT @.key = MIN(keyvalue) FROM #keys

WHILE @.key IS NOT NULL
BEGIN
SET @.sql = @.sql + ',' + @.NEWLINE +
' SUM(CASE CAST(' + @.oncols +
' AS nvarchar(100))' + @.NEWLINE +
' WHEN N''' + @.key +
''' THEN ' + CASE
WHEN @.sumcol IS NULL THEN '1'
ELSE @.sumcol
END + @.NEWLINE +
' ELSE 0' + @.NEWLINE +
' END) AS c' + @.key

SELECT @.key = MIN(keyvalue) FROM #keys
WHERE keyvalue > @.key
END

--7
SET @.sql = @.sql + @.NEWLINE +
'FROM ' + @.table + @.NEWLINE +
'GROUP BY ' + @.onrows + @.NEWLINE +
'ORDER BY ' + @.onrows

-- PRINT @.sql + @.NEWLINE -- For debug
EXEC (@.sql)

GO

--First option
Use pubs
GO

create table strings
(
groupcol char(1) not null,
keycol int not null,
string varchar(10) not null
)
GO

insert into strings values('a', 11, 'strA1')
insert into strings values('a', 152, 'strA2')
insert into strings values('b', 101, 'strB1')
insert into strings values('b', 201, 'strB2')
insert into strings values('b', 307, 'strB3')
insert into strings values('b', 499, 'strB4')
GO

select groupcol,
max(case when rownum = 1 then string end) as str1,
max(case when rownum = 2 then string end) as str2,
max(case when rownum = 3 then string end) as str3,
max(case when rownum = 4 then string end) as str4,
max(case when rownum = 5 then string end) as str5
from (select *, (select count(*)
from strings as s2
where s2.groupcol = s1.groupcol
and s2.keycol <= s1.keycol) rownum
from strings as s1) as s
group by groupcol
GO

DROP Table strings

GO|||Thanks a lot|||I use the second option (dynamic execution)|||I use stored procedure "sp_CrossTab", is OK (testing with "SQL ExecMS")

If I want to use this stored procedure in VB , did not returns a recordset.

This is VB Code: (Where have I did it wrong ????)

Private cn As New ADODB.Connection
Private cmd As New ADODB.Command
Private rs As New ADODB.Recordset

'--------------------
Private Sub Form_Load()

Dim SirConectare_SQL As String

SirConectare_SQL = "Provider=SQLOLEDB.1" & _
";Integrated Security=SSPI" & _
";Persist Security Info=False" & _
";Initial Catalog='" & "Test" & "'" & _
";Data Source='" & "Acasa" & "'"

With cn
.ConnectionString = SirConectare_SQL
.Open
.CursorLocation = adUseClient

End With

End Sub
'-----------------------
Private Sub Command1_Click()

cmd.ActiveConnection = cn
cmd.CommandText = "sp_CrossTab"
cmd.CommandType = adCmdStoredProc

cmd.Parameters(1).Value = "Table1"
cmd.Parameters(2).Value = "Day"
cmd.Parameters(3).Value = "XXXXX"
cmd.Parameters(4).Value = "Grup"
cmd.Parameters(5).Value = "Value_1"

Set rs = cmd.Execute

MsgBox rs.RecordCount

End Sub|||I forgot this ...

set nocount on

.....................

It is OK, thanks