Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70

    Unanswered: Pivot Table / CUBE ?

    Please see the attached file
    Attached Thumbnails Attached Thumbnails file_1.jpg  

  2. #2
    Join Date
    Jul 2002
    Posts
    63
    There 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

  3. #3
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70
    Thanks a lot

  4. #4
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70
    I use the second option (dynamic execution)

  5. #5
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70
    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

  6. #6
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70
    I forgot this ....

    set nocount on

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

    It is OK, thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •