How can i write a query to split a database column and shows 2 new columns. In my database column
I have 2 mixing items and need to split out to 2 columns. Normally I have to write a query and change parameter
and run another query.
For example a database column with average number and range number.
Thanks
Daniel
Can you post some DDL, sample data and expected results?
AMB
|||
Hai,
Can you try the below query, and let me know that, it relates to your requirement or not:
DECLARE @.Columns varchar(1000)
SET @.Columns = ''
-- Create a temporary table.
CREATE TABLE #TempTable(Items varchar(50))
INSERT INTO #TempTable(Items) VALUES('A')
INSERT INTO #TempTable(Items) VALUES('A')
INSERT INTO #TempTable(Items) VALUES('A')
INSERT INTO #TempTable(Items) VALUES('B')
INSERT INTO #TempTable(Items) VALUES('B')
INSERT INTO #TempTable(Items) VALUES('B')
INSERT INTO #TempTable(Items) VALUES('C')
INSERT INTO #TempTable(Items) VALUES('C')
INSERT INTO #TempTable(Items) VALUES('D')
INSERT INTO #TempTable(Items) VALUES('D')
-- Before
SELECT * FROM #TempTable
-- Make a column list
SELECT
@.Columns = @.Columns + '[' + Items + '], '
FROM #TempTable
GROUP BY Items
-- Check the column values exits or not.
IF ( @.Columns IS NOT NULL ) AND ( @.Columns <> '' )
BEGIN
DECLARE @.Query nvarchar(1000)
SELECT @.Columns = SUBSTRING(@.Columns,1, LEN(@.Columns)-1)
SELECT @.Query = '
SELECT
*
FROM
(
SELECT
Items
FROM #TempTable
) AS Dummy
PIVOT
(
MAX(Items)
FOR Items IN (' + @.Columns + ')
)AS PvtTable'
EXEC(@.Query)
END
-- Drop the temporary table.
DROP TABLE #TempTable
Please clarify If I did any wrong.
Regards,
Kiran.Y
|||Perhaps something like:
SET NOCOUNT ON
DECLARE @.MyTable table
( RowID int IDENTITY,
MyGroup int,
MyValue decimal(10,2)
)
INSERT INTO @.MyTable VALUES ( 1, 25 )
INSERT INTO @.MyTable VALUES ( 2, 5 )
INSERT INTO @.MyTable VALUES ( 1, 10 )
INSERT INTO @.MyTable VALUES ( 1, 15 )
INSERT INTO @.MyTable VALUES ( 1, 4 )
INSERT INTO @.MyTable VALUES ( 2, 6 )
INSERT INTO @.MyTable VALUES ( 2, 11 )
INSERT INTO @.MyTable VALUES ( 2, 0 )
INSERT INTO @.MyTable VALUES ( 1, 12 )
SELECT
Average = cast( avg( MyValue ) AS decimal(10,2)),
Range = ( cast( min( MyValue ) AS varchar(10)) + '-' +
cast( max( MyValue ) AS varchar(10)))
FROM @.MyTable
GROUP BY MyGroup
Average Range
13.20 4.00-25.00
5.50 0.00-11.00
|||
Hi Kiran
Thanks for answering my email. To clarify this below are my tables and columns and my query
Table: Item Stat_label Stat_value
column: Pack ID Stat_label_ID Stat_value_ID
Pack_Num Label ( has 2 rows Value
Ave and Range)
My query to list Pack_Num, Ave and it's value
SELECT Item.Pack_Num, Stat_label.Label, Stat_value.Value
FROM Item, Stat_label, Stat_value
WHERE Item.packID=Stat_label.Stat_label_ID AND
Stat_label.Stat_lavel_ID=Stat_value.Stat_value_ID
AND Stat_label.Label= Ave
My question: I want a query to list Pack_Num, Ave, Range and value
How can I do it?
That's mean this query need to split the Stat_label and list another
column name"Range".
Thanks
Daniel
If you are using SQL 2005, look into the PIVOT function.
If you are using SQL 2000, explore using CASE.
Maybe these articles will help:
Pivot Tables -A simple way to perform crosstab operations
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1131829,00.html
Pivot Tables - How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574
Pivot Tables -Dynamic Cross-Tabs
http://www.sqlteam.com/item.asp?ItemID=2955
Pivot Tables - Crosstab Pivot-table Workbench
http://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/
|||
Thanks all
I can not use "insert" because my account for this is read only and I avoid to list everything in a column and and use Excel pivot to summary.
Daniel
|||
Daniel,
If you would carefully examine the code provided, you will see that the INSERT statements are only building a sample table so that we could demonstrate a query suggestion.
You didn't bother to provide the table DDL, or sample data, so we have to waste our time creating sample data for you. and apparently, you can't read and understand example code.
|||
This may be closer to what you are hoping to find:
SELECT
i.Pack_Num,
sl.Stat_Label,
Average = cast( avg( sv.Stat_Value ) AS decimal(10,2)),
Range = ( cast( min( sv.Stat_Value ) AS varchar(10)) + '-' +
cast( max( sv.Stat_Value ) AS varchar(10)))
FROM Item i
JOIN Stat_Label sl
ON i.Pack_ID = sl.Stat_Label_ID
JOIN Stat_Value sv
ON sl.Stat_Label_ID = sv.Stat_Value_ID
WHERE sl.Label = 'Ave'
GROUP BY
i.Pack_Num,
sl.Stat_Label
|||
Thanks Anrnie but It is not working
Error at Average= cast......
Error at Range= (cast......
My Average and Range are decimal, no need cast
Do I have to declare a temp table?
Daniel
|||
Actually, it appears that the Stat_Value is most most likely a varchar().
Before we can help you any further, please post the table DDL and some sample data in the form of INSERT statements. Please refer to this link for help in preparing your material.
|||
Can SQL query create a new column or not?. DO NOT want to make a temp table.
Thanks
Daniel
|||
Can TSQL create a new column at the output?
If not I need 2 select statement but how to joint them? Can not use EXCEPT in TSQL? Tried to use UNION but
the results in one column.
It's complicated with creating a temp table since I do not know how to insert to temp table from database.
Thanks
Daniel
