Frequency distributions segmented by a field
For the first challenge, are you looking for something like this?

SELECT wgt, SUM(Case when household_size=1 then 1 else 0 end) AS SIZE1
    , SUM(Case when household_size=2 then 1 else 0 end) AS SIZE2
    , SUM(Case when household_size=3 then 1 else 0 end) AS SIZE3
    , SUM(Case when household_size=4 then 1 else 0 end) AS SIZE4
FROM tablename
GROUP BY wgt


Now for the fancier challenge, where @column will be your sp parameter:

DECLARE @column VARCHAR(100);
SET @column = 'household_size';
DECLARE @main_SQL VARCHAR(4000);

SET @main_SQL =
    'DECLARE dynamic_cursor CURSOR FOR 
    SELECT DISTINCT ' + @column + '
    FROM tablename 
    ORDER BY ' + @column + ' 

    --Now we will be using a cursor to iterate over 
    DECLARE @value INT; 
    OPEN dynamic_cursor 
    FETCH NEXT FROM dynamic_cursor INTO @value 

    DECLARE @sql VARCHAR(2000); 
    SET @sql = ''SELECT wgt''; 
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
        SET @sql = @sql + '', SUM(Case when ' + @column + '='' + CAST(@value AS VARCHAR) + '' then 1 else 0 end) AS SIZE'' + CAST(@value AS VARCHAR) 
        FETCH NEXT FROM dynamic_cursor INTO @value 
    END 
    SET @sql = @sql + '' FROM tablename GROUP BY wgt'' 

    close dynamic_cursor 
    DEALLOCATE dynamic_cursor 

    EXEC(@sql) '

EXEC(@main_SQL)


This part selectes the distinct values of the tablename's column received into a cursor  

DECLARE dynamic_cursor CURSOR FOR 
SELECT DISTINCT ' + @column + '
FROM tablename 
ORDER BY ' + @column + ' 


Next we will iterate over the distinct values to dynamically build up a query that will look just like the one at the top of my post, so here is the basis for it:
start the select:

SET @sql = ''SELECT wgt''


the iterative part which dynamically adds the successive , SUM(Case when household_size=n then 1 else 0 end) AS SIZEn to the select (note that @value will be the distinct values that we got selected into the cursor):

SET @sql = @sql + '', SUM(Case when ' + @column + '='' + CAST(@value AS VARCHAR) + '' then 1 else 0 end) AS SIZE'' + CAST(@value AS VARCHAR)


finish the select:  

SET @sql = @sql + '' FROM tablename GROUP BY wgt''